SQL Server in Domain 1

[spelk]

Can someone give me a brief overview of how you have your SQL Server log into your domain.

I have placed the SQL Server W2003 Server into AD as a Server, do I have to create an account on the DC for the SQL Server to authenticate? Do you log in your SQL Server under your admin account or under your domain account for that machine?

I'm a bit puzzled by how this setup is configured. As I understand it, the SQL Server will use Windows authentication via AD on the domain controller, but how does the SQL Server itself log in to the domain and what account does it run under during normal operations?

I realise its a bit of a newbie question, and I apologise, but any help with this would be most appreciated.

 

[SimonDavies]

I think you're getting a little mixed up with your thinking.

If the server is a member of the domain it will use that account to authenticate itself, as far as SQL goes it depends on the authentication method used, you can have it as SQL Authentication (using an SA account), Windows Only (windows accounts) and Mixed Mode (obviously enough a mix of either Windows or SQL accounts).

The SQL server itself will use the local system account to run the actual instance, if you need to have other resources use that SQL instance that aren't local to it you would need to create Service Principle Name (SPN) entries for those services.

I think you need to explain a little more what it is you're trying to do exactly.

Simon

The real world is not about exam scores, it's about ability.

 

[spelk]

We have a server as the domain controller, and a server for our SQL Server databases. We have about 80 users in the Active Directory on the domain controller (which is also used for file sharing and printer sharing/management). We want Windows Only authentication with the databases. I'm confused as to what account the SQL Server will run under, and how it is connected to the domain controller to authenticate via the accounts in the Active Directory.

 

[itsp1965]

As per what Simon indicated;

- The SQL server services themselves runs under the Local System account on the server where SQL server is installed by default (check the services MMC for confirmation) Unless you have indicated another account during installation it will be the Local System Account

- As for SQL server logins using Windows authentication; if your SQL server is part of the domain, then you will be using domain accounts. If the server is still in workgroup mode (ie not added to the domain) then you will be using accounts local to that server.

 

[spelk]

Ah ok thank you (and Simon) for clarifying that for me.

So, you log the SQL Server into the local admin account, and its added to the domain to allow windows authentication for the users.

Much appreciated folks.

 

[SimonDavies]

No that's not quite how it works.

If you have a group of users who are part of the local machine Admins (for instance the Domain Admins group is by default a member of the local admin group on each server in the domain) then those users will be granted access to the SQL Server instance, however that's only if you're using Windows or Mixed Mode authentication, there are situations where having a SQL server using Windows only authentication won't let Domain Admins log into the server, the Domain Admins are obviously local admins on the machines, the Local Admin Group is allowed to access the SQL server... but for whatever reason if someone logs onto the server with a Domain account that account can't run the SQL Admin Tools (SQL Management Studio), log on locally however and they can.

You don't do anything about adding the SQL server software onto the Domain however, you add the Server itself and then ideally you want to install SQL then.

Simon

The real world is not about exam scores, it's about ability.

 

[mrdenny]

SQL Server can be setup to run an either a Local Windows account on the server, a Domain Account, or the Local System Account. If the server is a member of the Windows domain then it doesn't matter which account you run it under. It will always allow you to use Windows Authentication assuming that the account which is attempting to log into the SQL Server has the rights needed to log into the SQL Server.

It is recommended that you not run the SQL Server under the Local system account as this gives the SQL Server more rights than it needs to run which presents a security problem unto it self.

If your SQL Server will not need to directly access network resources then it is common to run the SQL Service under a local account. If you will be accessing network resources then you'll need to run the SQL Server under a domain account.

This begins to change when you have several servers as you want to make password management easier, so you would typically run all the SQL Services on all the SQL Servers under a single domain account.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog