SQL Server Security Basics? 1

[35mph]

At the risk of exposing my COMPLETE ignorance, I find that I MUST break down and ask for a simplified explanation of a few SQL Server security topics.

Basically, I find that "security" is addressed in several locations... and I'm just plain confused on what each means. For example:

Under Edit SQL Server Registration Properties, under the "Connection" topic, there is Windows or SQL Server Authentication. That in and of itself is clear to me, but then throw in Properties > Security where SQL Server and Windows versus Windows Only are choices, and I become confused.

Then there is the Startup Service Account in Properties where you choose System or This Account.

Can someone please explain the basics of these topics to me? And please... I HAVE read the 70-228 book (Administering SQL Server)... and I must have missed something in 1st grade, because I still jsut don't get it.

My ignorance may be due to the limited nature of our installation... for example, we only have Enterprise Mananager installed on the same machine that SQL Server is installed on... and our entire security configuration is "default" at best. Maybe if your explanation included a configuration with a little more complexity, that would help illustrate.

Thank you!

 

[mrdenny]

Your confussion is normal. There are a lot of security screens, and they aren't all well explained.

The Edit SQL Server Registration Properties this is how the Enterprise Manager will connect to the SQL Server. Something to keep in mind with Enterprise Manager is that as far as the SQL Server is conserned, it's just a client connecting and running T/SQL code against the SQL Server. Anything you can do in Enterprise Manager can be done directly through code.

In the Properties > Security page where is says SQL Server and Windows or Windows only this is configuring the SQL Server it self and telling it what kinds of logins to allow. Windows only is more secure, as it does not allow ANY none-domain or none-windows logins. However it does disable the SA account (which is both good and bad).

The Startup Service Account is the user account that Windows will be using to run the SQL server. When you run any service (SQL Server is just a service on the server) it must be run under a user account. If it doesn't need a specific user account to run, it can be run under the System Account. This is an account which is restricted to the local server. The disadvantage to this is that you can not access network resources. For example if you run the SQL Server under a user account (most companies setup an account like DOMAIN\SQLService or DOMAIN\SQLServer to run the SQL Server under) you can backup your databases to a network share, or schedule imports from a network share. If you don't need to access a network share or another SQL Server you can use the system account with out much problem. If you will be sending email via the xp_sendmail procedure, or using the alerts, and operator notification you'll need to use a domain account.

Feel free to install Enterprise Manager on your workstation and manage the SQL Server from there. You'll find that it makes life much easier.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[35mph]

Thanks for that information. Here are a couple of follow-up questions:

1. In the SQL Server Registration Properties, since it is possible (probable?) that EM would be installed on one or more workstations (for a team of SQL Server admins), would it be likely that each workstation (SQL Server Registration Properties) would have a different login... such as one associated with that particular user's workstation?

2. We use an accounting application that FREQUENTLY requires a user to login as 'sa.' Would that pretty much imply that we need to allow mixed-mode (SQL Server and Windows) logins?

3. Regarding the startup service account, if we set up an account such as DOMAIN\SQLServer, would it be likely that the only time that account got used was when the machine started up, and started SQL Server?

4. And if we used xp_sendmail and/or alerts, we would use a domain account, DIFFERNET from DOMAIN\SQLServer? In fact, THAT account could be the same as those admins would use for Enterprise Manager (i.e. DOMAIN\jsmith)

5. When would we use the 'sa' account in any of these places... recommended never? (Unless in special, gotta have all the power, situations...)

 

[mrdenny]

1. EM can be installed on any machine you want to be able to manager the SQL Servers from. Each user should be using thier own account to connect to the SQL Server. The easiest way is to grant access based on Windows accounts. That way people won't share accounts. Most people will share an application username and password, but not thier network username and password.

2. Call the vendor and find out how to configure the app to connect with another account besides sa. Nothing and no-one should EVER connection with the sa account. If you are using the account you are in mixed auth mode.

3. Sort of. When SQL performs some actions it will go and authenticate against the domain controllers again. Such as if you use xp_cmdshell, run a DTS package, run a job, etc. These all require something to be authenticated against the domain. Also the SQL Agent will connect to the SQL Server every once and a while. This will also require authentication to work.

4. Incorrect. If you want to use xp_sendmail and/or alerts you'll be setting up the mail profile with the same account that the SQL Server runs under (DOMAIN\SQLServer in this case). See my FAQ on setting up email faq962-4452.

5. Never. The sa account shouldn't ever be used. The only exception is if the SQL Server can not authenticate against the domain for some reason, and you need to figure out why. Most DBAs don't need the sa password. Simply grant their Windows accounts sysadmin rights by adding them to the sysadmin server role.

As an example at my last job thier were 6 DBAs, only 2 had the sa password. The only reason to use the sa password instead of a personal account with admin rights is to make changes to the SQL Server without those changes being logged under your account.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[35mph]

Excellent answers... You are a scholar and a gentleman. Thank you!

 

[mrdenny]

no problem, and thanks.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)