In our first part we have discussed about the SQL Server Password Security which is the first part of securing SQL Server 2008 R2.
Encrypting Client Connection Strings
While using Windows authentication is the best way to connect to the database server, this isn’t always possible because the client machine that is connecting to the database server may not be connected do the Windows Domain.
This is most often the case when the web server is located in a DMZ network and the database server is located within the internal network as shown in Figure below.
In a case like this, the application development team should take extra care to secure the web server’s connection string. Without this extra protection, someone could break into the web server and find the database server’s connection information sitting in the web.config file and simply log into the database using the username and password, which are stored in plain text in the configuration file. One great technique to do this is to have the web application on startup read the web.config file looking for an unencrypted connection string. Then read that string into memory, delete that node from the web.config file’s XML, and then add a new node labeled as being the encrypted string, encrypt the string, and place the encrypted string within the XML document, saving it when done. On subsequent loads of the XML file, the unencrypted connection string would not be found, and the application would then load the encrypted version, decrypting it in memory, thereby making it much, much harder for someone who has broken into the SQL Server to find any useful connecting string information. If you don’t want to give the web application access to write to the web.config file (as this would technically be a security hole unto itself), the application team could create a small standalone app that takes the normal connection string and outputs an encrypted value, which the SA could then put within the web.config file during deployment of the application by the SA team.
SQL Reporting Services
SQL Reporting Services does an excellent job of protecting the connection information to the repository databases, as well as the connection strings that the reports use to connect to the source databases. All database connection strings that are used by SQL Reporting Services are encrypted and stored within the web.config as the encrypted string. Within the SQL Server Reporting Services database, typically named ReportServer, all the connection information that the reports use to connect to the
source databases is also stored as an encrypted value. Both of these encrypted values together form a very secure platform that makes it very difficult for an attacker to exploit the SQL Server Reporting Services platform to get any useful information from the database holding the Reporting Server catalog database, or the source data; getting access to the source data via the data stored within the SQL Server Reporting Service repository would require decrypting two layers of information.
Application Roles
When using Windows Authentication, there is an unfortunate side effect that needs to be considered: The user can now log into the database using any Open Database Connectivity (ODBC) based application such as Microsoft Access, Microsoft Excel, and SQL Server Management Studio, and they have the same rights that they would have if they were logged in via the application. If the user logs into the database by supplying the SQL Login username and password, this name risk is there. However, if the application contains the username and password hard coded within the application, then the user won’t have this ability as they will not have the username and password. This is probably something that you don’t want to happen. Before you go and switch all your applications to using SQL Authentication and hard coding the password within the application, there’s another solution that gives you the best of both worlds. This solution is to use an application role.
The application role is not a very well-understood, and therefore not very frequently used, security feature of Microsoft SQL Server, which allows a user to authenticate against the Microsoft SQL Server Instance, but not have any specific rights
within the database. The rights to perform actions are granted to the application role, which would then need to be activated by the application before the user would be able to perform any actions.
Application roles are created by using the sp_addapprole system stored procedure in SQL Server 2000 and below or by using the CREATE APPLICATION ROLE statement in SQL Server 2005 and above. The application role has its own password that is used to ensure that only authorized applications are able to activate the application. The application role is activated by using the sp_setapprole system stored procedure, and then the application role is deactivated by using the sp_unsetapprole system stored procedure, or by simply closing the connection to the database engine.
Here is sample code using the sp_addapprole system stored procedure and CREATE APPLICATION ROLE statement to create an application role:
EXEC sp_addapprole @rolename=’MyAppRole’, @password=’MyPa$$word’
CREATE APPLICATION ROLE MyAppRole WITH PASSWORD=’MyPa$$word’
The sp_setapprole system stored procedure has four parameters that are of interest. The first and second parameters are the @rolename and @password parameters to which you supply the name and password that were specified when you created the
application role. The third parameter is the @fCreateCookie parameter, which is a bit parameter and tells the SQL Server if it should create a cookie when the application role is activated (I’ll explain the cookies in a moment). The fourth parameter is
the @cookie parameter, which is a varbinary(8000) and stores the cookie that was created if the @fCreateCookie parameter was set to 1.
The @cookie parameter stores a cookie much in the same way that your web browser stores cookies when you browse the web, so that it can correctly identify the session that was used to activate the application role. Thus, when the application role is
disabled, the SQL Server knows which session state to return the user’s session to. If you don’t plan to unset the application role and will simply close the connection to the SQL Server, then you don’t need to set a cookie and can simply set the @fCreate Cookie password to 0 telling the SQL Server to not create the cookie.
In the sample code shown in next example, I created a new database, and then we create an application role within that database. We then create a table within the database, as well as a user within the database. We next give the application role access to select data from the table. We then use the EXECUTE AS statement to change your execution context from that of our user to that of the user, which we just created and has no rights. Next we query the table, which returns an error message to us. After that we switch to using the application role and try and query the table again, this time receiving the output as a recordset. We then unset the application role using the cookie that was created by the sp_setapprole system stored procedure.
We then use the REVERT statement so that we are no longer executing code as our MyUser database use, after which we drop the sample database.
USE master
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name =’AppRoleTest’)
DROP DATABASE AppRoleTest
GO
CREATE DATABASE AppRoleTest
GO
USE AppRoleTest
GO
CREATE APPLICATION ROLE MyAppRole WITH PASSWORD=’MyPa$$word’
GO
CREATE TABLE MyTable
(Col1 INT)
GO
CREATE USER MyUser WITHOUT LOGIN
GO
GRANT SELECT ON MyTable TO MyAppRole
GO
DECLARE @cookie varbinary(8000)
EXECUTE AS USER = ‘MyUser’
SELECT * FROM MyTable
EXEC sp_setapprole @rolename=MyAppRole, @password=’MyPa$$word’, @cookie=@cookie OUTPUT, @fCreateCookie=1
SELECT * FROM MyTable
EXEC sp_unsetapprole @cookie=@cookie
REVERT
GO
USE master
GO
DROP DATABASE AppRoleTest
GO
When we run this script as shown in text output mode from within SQL Server Management Studio, we see the output shown in next image. The first SELECT statement that we issued was rejected because the user didn’t have rights to the table dbo.MyTable in the AppRoleTest database. However, the second SELECT statement that we issued after we set the Application Role was accepted by the database, and the contents of the table were returned.
You can now see how use of the application role can enable the use of the very secure Windows authentication without requiring that the user’s Windows account actually have rights to access any objects within the database directly, but the application
can run once the application role has been activated.
Another technique that can be used along the same lines of the application role is to create a user with no attached login
and use the EXECUTE AS statement to run commands as that user. While this will allow you to run all your
statements without the user needing to have rights to the database objects, the problem with this technique is that any logging that is done via the username functions returns the dummy user that you created and not the login of the actual
user. This is shown along with sample code in next image As you can see in the sample code, we create a dummy user, then output my username using the SUSER_SNAME() system function, then switch to running under the context of the MyUser
database user, and then output the value of the SUSER_SNAME () function again with the output being the SID of the MyUser database user account. You can’t even query the dynamic management views to get the correct username of the user
logged in, because once the EXECUTE AS has been executed, the dynamic management views show the SID of the user instead of the name of the login that was originally connected to the database.
When using an application role, you don’t have the database username return problem when using the system functions or the dynamic management views.
How to use Windows Domain Policies to enforce password length
Starting with Microsoft SQL Server 2005, Microsoft introduced a new level of password security within the product, as this was the first version of Microsoft SQL Server that could use the domain policies to ensure that the passwords for the SQL
Authentication accounts were long enough and strong enough to meet the corporate standards as set forth by the SAs. By default, all SQL Authentication accounts created within the SQL Server instance must meet the domain password security policies. You can, if necessary, remove these restrictions by editing the SQL Authentication account. Within the Microsoft SQL Server, two settings can be applied to each SQL Authentication Login, which are shown in next screenshot.
-
The “Enforce password policy” setting tells the SQL Server engine to ensure that the password meets the needed complexity requirements of the domain, and that the password hasn’t been used within a specific number of days, which is defined within the domain policy, and is explained later in this article.
- The “Enforce password expiration” setting tells the SQL Server that the password for the SQL Authentication Login should have expired based on the domain settings. The “User must change password at next login” option, shown disabled in the image above, will only become available when the logins password is manually reset and the “Enforce password policy” setting is enabled for the login. Allowing the SQL Server to ensure that your passwords meet your domain policies has some distinct advantages, especially when it comes to auditing. Without this ability you would need to physically check each SQL server password to ensure that It meets the corporate standards when the Auditor asks you if all your SQL Authentication passwords meet the corporate standards. In a worst case situation, this would require that you either keep a list of all the usernames and passwords somewhere (which would probably cause you to fail the audit) or you would need to contact each person that uses the SQL Authentication login and ask them how long the password is, and if it meets the company policies, and so on. Now with this feature built into the product, a quick and simple SQL query is all that it takes to verify the information.
Querying the sys.sql_logins catalog view will show you any logins that may not meet the domain password policies:
SELECT name, is_policy_checked FROM sys.sql_logins
While the T/SQL shown in next example works great for a single SQL Server, if there are dozens or hundreds of SQL Servers that need to be verified, a T/SQL script may not be the best way to check all those servers. In this case a Windows PowerShell script may be more effective. Within the Windows PowerShell script shown in next example, the SMO (Server Management Object) is used to get a list of all the available instances on the network.
After this list has been returned from the network, SMO is used to return the SQL Logins along with the value of the PasswordPolicyEnforced setting.
Example: “Using SMO to return the PasswordPolicyEnforced setting for all SQL Logins for all SQL Server Instances available on the network.”
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.Smo’) j out-null
foreach ($InstanceList in [Microsoft.SqlServer.Management.
Smo.SmoApplication]::EnumAvailableSqlServers())
{
$InstanceList;
$instanceName ¼ $InstanceList.Name;
$instanceName;
$SMOserver ¼ New-Object (‘Microsoft.SqlServer.Management.
Smo.Server’) $instanceName
$db ¼ $SMOserver.Logins j where-object {$_.loginType -eq
“sqllogin”} j select name, PasswordPolicyEnforced
$db;
}
By setting the is_policy_checked flag to true (shown as the number 1 when you run the sample query), this tells you that any password that is assigned to the SQL Authentication Login must meet the password requirements of the domain. Expanding on the query shown in Example “By setting the is_policy_checked flag, this tells you that any password that is assigned to the SQL Authentication Login must meet the password requirements of the domain. Expanding on the query shown in the example above SQL Server Reporting Services report could be configured that runs against each SQL Server in the environment, giving a simple report that can be run as needed for auditing purposes.
When you have the is_policy_checked flag set to true, there are several domainwide settings that will be evaulated each time the password is changed. These policies can be found by editing the Group Policy Object (GPO) on the domain that holds these, an SQL Server Reporting Services report could be configured that runs against each SQL Server in the environment, giving a simple report that can be run as needed for auditing purposes.
When you have the is_policy_checked flag set to true, there are several domainwide settings that will be evaulated each time the password is changed. These policies can be found by editing the Group Policy Object (GPO) on the domain that holds these
settings, or by editing the local security policies for the server in question if that server is not a member of a Windows domain. While you can set these settings on a server that is a member of the domain, doing so won’t have any effect as the domain policies but will overwrite any local settings you have set.
If all the SQL Server Instances that need to be polled are registered within SQL Server Management Studio, this select statement can be run against all the instances at once returning a single record with all the needed information. This can be done by opening the registered servers panel within SQL Server management studio by clicking on the View dropdown menu and then the “Registered Servers” menu item. Right click on the folder that contains the SQL Server Instances you want to execute the query against and select “New Query” from the context menu that opens. This opens a new query window which, when executed, will execute the query against all the servers that are within the registered servers folder with all the data from all the servers being returned as a single recordset.
SQL Server Management Studio will automatically add in a new column at the beginning of the recordset, which contains the name of the instance; this will allow you to use the same query shown in Example 3.4 against all the SQL Servers at once and
giving back a single recordset that can be reviewed or handed off as needed.
Windows Authentication Group Policies
There are a total of six policies that you can set within Windows that affect the domain or local password policy. However, Microsoft SQL Server only cares about five of them. The policy with which the SQL Server is not concerned is the “Store
passwords using reversible encryption” policy. This policy tells Windows if it should store the user’s password using a two-way encryption process, instead of a one-way hash. Enabling this policy presents a security vulnerability on your domain as an
attacker could download the list of all users and passwords, then break the encryption on the passwords and have full access to every user’s username and password. Due to the security issues with this setting, the setting is disabled by default and should
remain so unless there is a specific reason to enable it. The typical reasons to enable it include using Challenge Handshake Authentication Protocol (CHAP) through Remote Access or Internet Authentication Services (IAS). It is also required if one or
more Internet Information Service (IIS) servers within the Windows Domain are using Digest Authentication.
The five password policies that the SQL Server does recognize and follow are the following:
- Enforce password history;
- Maximum password age;
- Minimum password age;
- Minimum password length;
- Password must meet complexity requirements.
Each of these settings has a specific effect on what the passwords can be set to and should be fully understood before changing the password of an SQL Authentication Login. The “Enforce password history” setting on the domain (or local computer) is not a boolean, although the name sounds as though it would be. It is in fact the number of old passwords for the account that the SQL Server should track so that passwords cannot be reused. The setting has a valid range of 0 (or no passwords) to 24 passwords. The more passwords that are kept, the greater the chance that the user will forget their password, but the lesser the chance that someone will break into the system via an old password. The default on the domain is 24 passwords.
The “Maximum password age” setting tells the SQL Server howmany days a password is valid. After this number of days has passed since the last password change, the user will be prompted to change the password. If the password is not changed, the user will not be able to log into the database instance. This setting accepts a value from 0 (never expires) to 999 days, with a default value of 42 days. The “Minimum password age” setting tells the SQL Server how many days from the time a password has been changed until it can be changed again. This setting prevents the user from rapid-fire changing their passwords to eat up the number of passwords specified by the “Enforce password history” setting. Without this setting, or with this setting set to 0, when the user’s password expires, the user can simply change the password 24 times and then change it to the same password that it was before effectively breaking the password requirement feature. This setting accepts a value from 0 (allows immediate password changes) to 998 days, with a default value of 1; however, this setting has a practical upper limit of one day lower than the setting for the “Maximumpassword age.” If you were to set this setting to the same value or higher than
the “Maximumpassword age” setting, then the users wouldn’t ever be able to login until after their passwords had expired. The “Minimum password length” setting tells the SQL Server how many characters need to be in the password for the password
to be acceptable. This setting can be any value from 0 (allowing a blank password) to 14 characters, with a default value of 7 characters. It is typically recommended to increase this value from the default of 7 to a higher number such as 9 characters.
While this will make the password harder for the user to remember, it will also make it exponentially harder for an attacker to guess. The “Password must meet complexity requirements” setting tells the SQL Server that all passwords must be considered
“strong” passwords. There are several requirements to having a strong password beyond what one would normally consider. By default this setting is enabled.
1. The password cannot contain the username within it.
2. The password must be at least six characters in length.
3. The password must contain characters from at least three of these four categories:
a. Lower-case letters (a through z);
b. Upper-case letters (A through Z);
c. Numbers (0 through 9);
d. Symbols ($, #, @, %, ^ for example).
When you enable the “Enforce password policy” setting for an SQL Authentication Login, this enforces the “Enforce password history,” “Minimumpassword length,” and “Password mustmeet complexity requirments” settings against that login. When you enable the “Enforce password expiration” setting for an SQL Authenticaiton Login, this enforces the “Maximum password age” and the “Minimum password age” settings against that login. In order to enable the “Enforce password expiration”
setting against an SQL Authenticaiton login, you must also enable the “Enforce password policy” setting. However, you do not need to enable the “Enforce password expiration” setting if you enable the “Enforce password policy” setting.
When working on an SQL Azure database, the login mustmeet the password complexity settings that Microsoft has defined. As of the summer of 2010, this means that the password must be 8 characters in length, and meet the complexity requirements
shown above. There is no way to configure a login to an SQL Azure instance to not meet these requirements, for the SQL Azure instances do not support using the check_policy parameter to disable the policy checking.
Summary
One of the biggest problems in today’s IT world is that once you have created your nice secure passwords, how do you track them? Those usernames and passwords are probably going to be documented somewhere, typically within an Excel sheet that is kept on a network share so that all the database administrators within the group have quick and easy access to them. However, by doing this you now have placed all the passwords that you have taken the time to ensure that are strong and secure within your web.config and app.config files are easily readable and usable by anyone who has access to the network share. Typically, not just the database administrators would have access to the network share. In addition to the database administrators, the SAs, backup software, and monitoring system would all have access to the network share. And this is in addition to whoever has found the lost backup tape for your file server. In other words, be sure to store that password list in a nice, safe place and not in the public arena available to everyone to read and network share.
Windows Management and Scripting 