Some of the settings that are listed in Server Properties dialog box in SQL Server Management Studio (SSMS) are stored in the local registry. These registry settings can be read from and written to by TSQL code. Thus, scripts can be created to standardize settings for all servers that you manage.
The purpose of today’s talk is talk about security page of the Server Properties dialog box and how a DBA can change the settings.
All of the settings can be changed by selecting the correct radial button, check box or entering in the correct text via the Server Properties dialog box in SSMS. Please see the image below.
With that said, I am going to go over how each of the settings and how they can be changed by TSQL code.
The server authentication (login mode) is stored in the registry under the MSSQLServer key. The actual full key is dependent upon the instance, version and edition of SQL Server.
The TSQL snippet below reads in the current mode and changes it to mixed mode. It uses the xp_regread and xp_regwrite undocumented system stored procedures.
| Login Mode | Registry Value |
| Windows | 0x00000001 |
| Mixed | 0x00000002 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
-- -- Read login mode -- -- Declare variable DECLARE @LoginMode1 int -- Read from the registry EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer', @value_name='LoginMode', @value=@LoginMode1 OUTPUT -- Show value SELECT @LoginMode1 AS Level -- -- Write login mode -- -- Declare variable DECLARE @LoginMode2 int -- Set the value SELECT @LoginMode2 = 2; -- Write 2 the registry EXEC master.dbo.xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer', @value_name='LoginMode', @type = 'REG_DWORD', @value = @LoginMode2; |
The login auditing is a very important setting that allows you to track both successful and failed logins in the SQL Server log.
The login auditing (audit level) is stored in the registry under the MSSQLServer key. The actual full key is dependent upon the instance, version and edition of SQL Server.
| Audit Level | Registry Value |
| None | 0x00000000 |
| Success | 0x00000001 |
| Failure | 0x00000002 |
| All | 0x00000003 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
-- -- Read audit level -- -- Declare variable DECLARE @AuditLevel1 int -- Read from the registry EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer', @value_name='AuditLevel', @value=@AuditLevel1 OUTPUT -- Show value SELECT @AuditLevel1 AS Level -- -- Write audit level -- -- Declare variable DECLARE @AuditLevel2 int -- Set the value SELECT @AuditLevel2 = 3; -- Write 2 the registry EXEC master.dbo.xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer', @value_name='AuditLevel', @type = 'REG_DWORD', @value = @AuditLevel2; </span> |
The server proxy account section is filled in if a account has been defined for xp_cmdshell
to run under. There are two ways to set this value.
One is to used the sp_xp_cmdshell_proxy_account system stored procedure and the other is to use the CREDENTIAL TSQL key word.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- Create the cmd shell proxy account (sp) EXEC sp_xp_cmdshell_proxy_account 'MyXpShellAcct', 'MyPassword' GO -- Drop the cmd shell proxy account (sp) EXEC sp_xp_cmdshell_proxy_account NULL; GO -- Select correct database USE [master] GO -- Create the cmd shell proxy account (TSQL) CREATE CREDENTIAL [##xp_cmdshell_proxy_account##] WITH IDENTITY = N'MyXpShellAcct', SECRET = N'MyPassword' GO -- Drop the cmd shell proxy account (TSQL) DROP CREDENTIAL [##xp_cmdshell_proxy_account##] go |
The security options at the end of the page can be set with the sp_configure system stored procedure. Like usual, make sure the ‘show advance options’ is enabled before executing the commands.
|
1 2 3 4 |
-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; GO |
The ‘common criteria compliance enabled’ setting adds the following security features to SQL Server.
- Residual Information Protection (RIP)
- The ability to view login statistics
- That column GRANT should not override table DENY
Please see msdn page for details.
|
1 2 3 4 |
-- Enable common criteria compliance EXEC sp_configure 'common criteria compliance enabled', 1; RECONFIGURE WITH OVERRIDE; GO |
The next two options come with SEVERE WARNINGS, this may cause system issues.
Enabling the C2 audit incures the overhead of writing failed and successful attempts to access statements and objects. Each log file grows to 200 mb. The process keeps on creating log files until the disk space on the server is all used up.
NOT GOOD!
SQL Server can be configured to allow ownership chaining between specific databases or across all databases inside a single instance of SQL Server. Cross-database ownership chaining is disabled by default, and should not be enabled unless it is specifically required. This option can open security holes.
NOT GOOD!
|
1 2 3 4 5 6 7 8 9 |
-- Enable C2 audit (WARNING - ADDITIONAL OVERHEAD & UNCHECKED STORAGE GROWTH) EXEC sp_configure 'c2 audit mode', 1 ; RECONFIGURE WITH OVERRIDE; GO -- Enable cross db ownership chaining (WARNING - POSSIBLE SECURITY HOLES) EXEC sp_configure 'cross db ownership chaining', 1; RECONFIGURE WITH OVERRIDE; GO |
In summary, the security page of SQL Server properties allows a DBA to configure the server. Using the xp_regread, xp_regwrite, and sp_configure commands allows one to create a script to have consistent settings between servers.
Next time, I will be talking about database paths that you can change via xp_regread and xp_regwrite.
I see so many articles with poorly written content that this one is refreshing. It’s great to know there are people that can write well and get their point across.
Rattling good info can be found on web blog.