By: K. Brian Kelley | Updated: 2013-08-06 | Comments (5) | Related: > Security
Problem
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.
Solution
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
- Check out the following items:
- MSSQLTips.com Security Tips (More than 100 and counting...)
- K. Brian Kelley 90+ Tips since 2008
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2013-08-06
