SQL Server Security Community Questions on Windows, Server Level, Database, Roles and more

By:   |   Updated: 2013-08-06   |   Comments (5)   |   Related: More > Security


I had the privilege to attend K. Brian Kelley's MSSQLTips.com Q&A session on SQL Server security.  I understand he was bombarded with security questions about Windows, Active Directory, Service Accounts, SQL Server Server Level, SQL Server Database Level, etc.  Is there any chance I can get my question answered?  Well you are in luck.  Check out this tip with answers to many of the communities SQL Server Security questions.


Windows, Active Directory, and Service Accounts Questions

Q: Doesn't the SQL Server service account need Administrator permission in Active Directory to register SQL Server's SPN and for registering the computer name?

The SQL Server service does not. By default, there are two classes of accounts that can create/delete SPNs in Active Directory.

  • Domain Admins
  • The computer accounts themselves (local System or Network Service, for instance)

The key phrase there is "by default." You can give the SQL Server service account the ability to register SPNs. This is covered in a Microsoft KB article: How to use Kerberos authentication in SQL Server. Look at Step 3: Configure the SQL Server service to create SPNs dynamically. Do note that although the article is for SQL Server 2000 and Windows Server 2003, it works for newer versions of SQL Server and for newer versions of Active Directory. The permissions are the same.

Q: Since the SQL Server service account shouldn't be a member of the local Windows Administrators group, should we treat the SQL Server Agent service account the same way (if different from the SQL Server relational engine service)?

Yes. The key here is to follow the Principle of Least Privilege. Basically, you give the rights to do the job - no more and no less. Neither the SQL Server service nor the SQL Server Agent service require Windows local administrator rights to run. There might be something that you're trying to do with SQL Server or SQL Server Agent that requires such rights, but by themselves they don't need that level of privilege. The actual rights the various services need can be found under the topic Setting Up Windows Service Accounts/a> in Books Online. I've linked to the SQL Server 2008R2 version.

Also, note that if you use the SQL Server Configuration Manager, it'll set the minimum rights necessary. It does not place the SQL Server Agent service account in the local Administrators group. If you're ever changing a service account, make sure you use this configuration tool.

Q: What built-in accounts are recommended to run the SQL Server engine and SQL Server agent (network service or local service)?

This is also covered in Setting Up Windows Service Accounts.. Local service is not a recommendation because the SQL Server engine will interact with Active Directory and also I believe some of the rights granted to SQL Server are beyond what Local Service normally has. SQL Server Agent, if it has any jobs that leave the server and the job is owned by a member of the sysadmin group, it will use the service account as well, thereby eliminating local service again. So why is local System mentioned? When local System leaves a computer, it appears as a computer name. So if the computer name is MySQLServer, it'll connect as an account that looks like MyDomain\MySQLServer$ (the $ at the end marks it as the computer account and not a standard user account).

With that said, I recommend against System for reasons I've detailed in this tip: Why System Account is a Bad Idea for SQL Server Service Account.

Q: Is it possible to grant linked server permissions to Windows user group?

Absolutely. A Windows group is no different from any other login. If you're having an issue, the problem is likely due to the fact that Kerberos delegation either isn't set up or is set up incorrectly. There's a whitepaper on how to set up Kerberos constrained delegation properly: How to Implement Kerberos Constrained Delegation with SQL Server 2008.


SQL Server Surface Area Questions

Q: What is your recommendation regarding the SQL Server extended stored procedure xp_cmdshell?

If you can, disable it. However, you're going to need to check periodically to make sure it stays disabled. I've covered some ways to try and prevent it from being enabled, but a sysadmin can always re-enable it: Can I stop a sysadmin from Enabling SQL Server xp_cmdshell?

Q: Do you recommend having the SQL Server sa login as database owners and also owners of all SQL Server Agent jobs?

Generally speaking, yes on the database side. The reason being that you don't want to create another account and since sa already has the rights, that being a database owner would give it, there's no other account running around with extra privileges. The exception is when you're using cross-database ownership chaining.  Since that keys ownership chaining at the login level, you'll want to think through database ownership because everything in the dbo schema (or any schema owned by dbo) will map up to sa. If you're not familiar with how cross-database ownership chaining works, I covered it in this tip: Understanding Cross-Database Ownership Chaining in SQL Server.

With respect to SQL Server Agent, it depends. The problem with a job being owned by sa is that if it does something within the SQL Server instance, it maps in as a sysadmin because it uses the SQL Server Agent service. There may be cases where you have jobs where you don't want that kind of access. These should not be owned by sa.

Q: I think it's a good idea change the ports, especially for remote access. What do you think about it?

This one also depends. To be blunt, if an attacker wants to discover your SQL Server, he can use a tool like nmap to do the appropriate port scans. There are other tools out there that do the job just fine, too. Therefore, don't depend on this "security by obfuscation." And if you're talking about within the internal network, if you have end users hitting the SQL Server, it's typically better to leave the port alone for a default instance. That means your SQL Server will play nicely with their tools without engaging second tier technical support to explain how to connect to a SQL Server.

If it's Internet facing, my view changes. Yes, the bad guys can and will still do port scans if they are actively targeting you. However, the vast majority of scans we see coming from the Internet are looking for well known ports. They are looking for "low hanging fruit", as we call it. Therefore, by using a different port you avoid all those scans. Therefore, it's worth the extra leg work to help get people connected.


SQL Server Security Questions - Server Level

Q: What's the best practice for using the SQL Server SA login? Can we disable it?

The best practice is not to use it to connect to SQL Server. Furthermore, if you can, configure SQL Server to use Windows authentication only. Whichever authentication mode you use, set a very strong (and long) password for sa.

With respect to disabling it, yes, you can, as this blog post from the SQL Server Engine team explains. Seriously consider doing so. However, though you *can* rename it, as that post instructs, I would not. We have seen cases where a renamed sa account has caused problems, as this post from the CSS SQL Server Engineers documents.

Q: A developer wants to execute a SQL Server stored procedure in debug mode. I believe SQL Server sysadmin level access required to use debug mode. I don't want to give sysadmin access to the developer. Is there any I can handle this without giving sysadmin access?

Unfortunately, there is not. Debugging requires sysadmin role membership, as the Visual Studio documentation specifies.

SQL Server Database Level Security Questions

Q: Hi. Could you explain a bit more about the implicit permissions of db_datareader & db_datawriter? I don't see the danger, if that's what the user needs.

If that's what a user needs, it's not an additional danger. However, there are two issues.

The first issue is that it's harder to audit. If I query sys.database_permissions, I won't see that members of these roles have any permissions. User-defined database roles will have permissions specified in this catalog view. So it's an additional step to remember the permissions. That's why I recommend roles be created with explicit permissions. Your auditors will thank you.

The second issue is if you have to have a new table or view where the user should not have access. If you're using explicit permissions and you're following the recommendation of setting permissions at the schema level, there's no issue. A new schema is created and the object is placed in it. Users don't have access to the new object. However, with db_datareader and db_datawriter, they have implicit access. So you either have to undo that and create the roles with explicit permissions or you have to start using DENY.

Q: What do you use to avoid the SQL Server fixed server roles db_owner, db_ddladmin, db_security_admin and to try to avoid db_datareader and db_datawriter?

The recommended practice is to create a role and assign explicit permissions. Typically speaking, all the permissions that these roles have are more than what users need. Now, if a user legitimately needs the permission one of these roles provides (with the exception of db_datareader or db_datawriter, which I answered in the previous question), use the fixed role.

Q: Are there any good scripts that will return all user permissions for a database or for a SQL Server?

There are, and I've got a bunch in the list of tips I've written. However, I'm trying to put together the "what you absolutely should be auditing" at the various levels. Those should be coming out in the next couple of months covering:

  • Server level
  • Database level
  • Outside of SQL Server

Q: Any concerns about the SQL Server public database role?

Yes, the general recommendation is to avoid using the public role. Every database user is a member of public. As a result, any permissions assigned to it are also assigned to every database user (both GRANT and DENY). In order to not get trapped in a situation where you have to undo permissions, it's better to go ahead and create your own roles and assign permissions accordingly.

Q: Will issuing a DENY privilege to a user override the db_datareader/db_datawriter SQL Server fixed database roles?

Yes, it will. The DENY permission still acts like a trump, even in the case of db_datareader or db_datawriter.

Q: Any recommendations or to be aware of T-SQL commands that come out of the box?

Obviously, at the database level, any of the CREATE statements. Permissions to CREATE also mean permissions to ALTER and DROP. Watch out for any scripts that grant CONTROL or IMPERSONATE. And when you see EXECUTE AS in code, such as a stored procedure, ensure you understand exactly what context that code will run under.

Q: Have you seen any performance hit using SQL Server Transparent Data Encryption (TDE)?

Given that you are performing more operations, there is some. One advantage to TDE is that the data is only encrypted in disk. So if it's in SQL Server's memory buffers, there's no repeated decryption going on. How much depends on what you're doing. Low I/O and low CPU workloads it is estimated to be about a 3-5% CPU performance hit. Some independent testing put it right around 7%. However, according to the this technical article, it could reach as much as 30% for cases where you already have high CPU usage: Database Encryption in SQL Server 2008 Enterprise Edition.

Next Steps

Last Updated: 2013-08-06

get scripts

next tip button

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources

Comments For This Article

Friday, August 09, 2013 - 11:05:29 AM - ARoot Back To Top (26184)

Thank you for pointing me to those topics.

I think you should have more information like this where you answer real world questions based on your experience.  I know people that have questions, but are too scared to ask.

Thursday, August 08, 2013 - 7:44:34 PM - K. Brian Kelley Back To Top (26163)

ARoot, I have three articles up here talking about the right algorithms / key length to use. If you hit my author page, you should see an article on HASHBYTES, one on understanding symmetric key algorithms, and one on asymmetric key algorithms. I give specific guidance there.

Thursday, August 08, 2013 - 7:43:31 PM - K. Brian Kelley Back To Top (26162)

sqlist, I'm going to have to disagree with you and Jeff. Both you and Jeff are thinking about it from a DBA's perspective. And when he uses the word "risk" he's not using it in the same way security folks are. From an INFOSEC perspective, the fact that I can go through SQL Server to get to a shell means xp_cmdshell is a security risk. On the same token, simply having SQL Server opened up to connect to the network is considered a risk to security types. Why? Because it is an exposed surface that can be attacked. In our parlance, risk is a potential security vulnerability or issue that needs to be looked at.

Back to the discussion about xp_cmdshell, one of the reasons we consider it a risk is SQL Server isn't intended to provide you with a shell. Through some functionality (xp_cmdshell) that's possible. However, that's not the intent. Worse, you get the shell with the same privileges as SQL Server and your SID is as the SQL Server service itself. Why is it important to classify it as a risk? Because then you decide what to do with it. This is where we get into calculations like ALE and the like in quantitative risk anaylsis. And remember, a malicious insider could be a DBA who couldn't normally get direct access to the files (this is why some orgs don't let them be admins on the servers where SQL Server is installed and don't give them RDP access, either). The use of xp_cmdshell allows them to bypass those other measures.

In addition, the reason you turn it off and try to monitor it is to stop the majority of attempts to use it. A determined attacker will get through. However, if someone is merely curious or if someone doesn't want to engage in a higher level of risk to themselves (think insider threat), they're not going to go tampering with xp_cmdshell if it's turned off. And that's the point with turning it off.


Thursday, August 08, 2013 - 2:24:21 PM - ARoot Back To Top (26155)

What about encryption? Do you have the same type of info on DB encryption?

Tuesday, August 06, 2013 - 3:33:15 PM - the sqlist Back To Top (26121)

In regards to xp_cmdshell security risks I only want to add Jeff Moden's insight of the matter:

"Turning off xp_CmdShell is a bit like putting a veil over rotting meat. It brings a false sense of security to the table and the flies can still get at the meat. Allow me to explain.

Who can use xp_CmdShell? That's right. Only people/app logins with "SA" privs or people that you made the horrible mistake of granting a proxy to can use it.

Next question. If you have xp_CmdShell turned off, who are the only people that can turn it back on? Correct again! Only people/apps with "SA" privs can turn it back on.

So, what's the real issue with xp_CmdShell being a security risk? The answer is xp_CmdShell is NOT a security risk. Poor security is the only security risk. If a hacker or an malicious internal user get's into the system with "SA" privs, then they can turn xp_CmdShell on in momements. Yeah, that action gets logged but that only provides documented testimony that security was grossly lacking to begin with.

Turning xp_CmdShell does nothing for security except to provide a chance for that part of a hackers code to turn it back on to run."


I'll say it again. xp_CmdShell is not a security risk. Only bad security is a security risk. Fix your security and then turn on xp_CmdShell. It's a wonderful tool and you're missing out on it because of bad security practices and myth."



Recommended Reading

Enabling xp_cmdshell in SQL Server

Understanding SQL Server fixed database roles

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

How to configure SSL encryption in SQL Server

SQL Server Permissions List for Read and Write Access for all Databases

get free sql tips
agree to terms