{"id":1332,"date":"2012-01-10T21:45:32","date_gmt":"2012-01-10T21:45:32","guid":{"rendered":"http:\/\/craftydba.com\/?p=1332"},"modified":"2017-10-12T12:34:16","modified_gmt":"2017-10-12T12:34:16","slug":"server-settings-%e2%80%93-part-2","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1332","title":{"rendered":"Server Settings \u2013 Part 2"},"content":{"rendered":"

Some of the settings that are listed in Server Properties dialog box in SQL Server Management Studio (SSMS<\/a>) 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. <\/p>\n

The purpose of today\u2019s talk is talk about security page<\/a> of the Server Properties dialog box and how a DBA can change the settings.<\/p>\n

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.<\/p>\n

\"\"<\/a><\/p>\n

With that said, I am going to go over how each of the settings and how they can be changed by TSQL code.<\/p>\n

The server authentication<\/a> (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. <\/p>\n

\"\"<\/a><\/p>\n

The TSQL snippet below reads in the current mode and changes it to mixed mode. It uses the xp_regread <\/a>and xp_regwrite<\/a> undocumented system stored procedures.<\/p>\n\n\n\n\n\n
Login Mode<\/td>\nRegistry Value<\/td>\n<\/tr>\n
Windows<\/td>\n0x00000001<\/td>\n<\/tr>\n
Mixed<\/td>\n0x00000002<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

\r\n-- \r\n-- Read login mode\r\n--\r\n\r\n-- Declare variable\r\nDECLARE @LoginMode1 int\r\n\r\n-- Read from the registry\r\nEXEC master.dbo.xp_regread\r\n  @rootkey='HKEY_LOCAL_MACHINE',\r\n  @key='SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQLServer',\r\n  @value_name='LoginMode',\r\n  @value=@LoginMode1 OUTPUT\r\n\r\n-- Show value  \r\nSELECT @LoginMode1 AS Level\r\n\r\n--\r\n-- Write login mode\r\n--\r\n\r\n-- Declare variable\r\nDECLARE @LoginMode2 int\r\n\r\n-- Set the value  \r\nSELECT @LoginMode2 = 2;\r\n\r\n-- Write 2 the registry\r\nEXEC master.dbo.xp_regwrite\r\n  @rootkey='HKEY_LOCAL_MACHINE',\r\n  @key='SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQLServer',\r\n  @value_name='LoginMode',\r\n  @type = 'REG_DWORD', \r\n  @value = @LoginMode2; \r\n<\/pre>\n<\/p>\n
The login auditing<\/a> is a very important setting that allows you to track both successful and failed logins in the SQL Server log.<\/p>\n
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. <\/p>\n\n\n\n\n\n\n\n
Audit Level<\/td>\nRegistry Value<\/td>\n<\/tr>\n
None<\/td>\n0x00000000<\/td>\n<\/tr>\n
Success<\/td>\n0x00000001<\/td>\n<\/tr>\n
Failure<\/td>\n0x00000002<\/td>\n<\/tr>\n
All<\/td>\n0x00000003<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
\r\n--\r\n-- Read audit level\r\n--\r\n\r\n-- Declare variable\r\nDECLARE @AuditLevel1 int\r\n\r\n-- Read from the registry\r\nEXEC master.dbo.xp_regread\r\n  @rootkey='HKEY_LOCAL_MACHINE',\r\n  @key='SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQLServer',\r\n  @value_name='AuditLevel',\r\n  @value=@AuditLevel1 OUTPUT\r\n\r\n-- Show value  \r\nSELECT @AuditLevel1 AS Level\r\n\r\n\r\n--\r\n-- Write audit level\r\n--\r\n\r\n-- Declare variable\r\nDECLARE @AuditLevel2 int\r\n\r\n-- Set the value  \r\nSELECT @AuditLevel2 = 3;\r\n\r\n-- Write 2 the registry\r\nEXEC master.dbo.xp_regwrite\r\n  @rootkey='HKEY_LOCAL_MACHINE',\r\n  @key='SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQLServer',\r\n  @value_name='AuditLevel',\r\n  @type = 'REG_DWORD', \r\n  @value = @AuditLevel2; \r\n<\/span><\/pre>\n<\/p>\n
The server proxy account section is filled in if a account has been defined for xp_cmdshell
\nto run under.  There are two ways to set this value.  <\/p>\n
One is to used the sp_xp_cmdshell_proxy_account <\/a>system stored procedure and the other is to use the CREDENTIAL <\/a>TSQL key word.<\/p>\n
\r\n-- Create the cmd shell proxy account (sp)\r\nEXEC sp_xp_cmdshell_proxy_account 'MyXpShellAcct', 'MyPassword'\r\nGO\r\n\r\n-- Drop the cmd shell proxy account (sp)\r\nEXEC sp_xp_cmdshell_proxy_account NULL;\r\nGO\r\n\r\n\r\n-- Select correct database\r\nUSE [master]\r\nGO\r\n\r\n-- Create the cmd shell proxy account (TSQL)\r\nCREATE CREDENTIAL [##xp_cmdshell_proxy_account##] WITH IDENTITY = N'MyXpShellAcct', SECRET = N'MyPassword'\r\nGO\r\n\r\n-- Drop the cmd shell proxy account (TSQL)\r\nDROP CREDENTIAL [##xp_cmdshell_proxy_account##]\r\ngo\r\n<\/pre>\n<\/p>\n
The security options at the end of the page can be set with the sp_configure<\/a> system stored procedure.  Like usual, make sure the ‘show advance options’ is enabled before executing the commands.  <\/p>\n
\r\n-- To allow advanced options to be changed.\r\nEXEC sp_configure 'show advanced options', 1;\r\nRECONFIGURE WITH OVERRIDE;\r\nGO\r\n<\/pre>\n<\/p>\n
The ‘common criteria compliance enabled’ <\/a>setting adds the following security features to SQL Server.<\/p>\n
    \n
  1. Residual Information Protection (RIP)<\/li>\n
  2. The ability to view login statistics<\/li>\n
  3. That column GRANT should not override table DENY<\/li>\n<\/ol>\n
    Please see msdn page for details.<\/p>\n
    \r\n-- Enable common criteria compliance\r\nEXEC sp_configure 'common criteria compliance enabled', 1;\r\nRECONFIGURE WITH OVERRIDE;\r\nGO\r\n<\/pre>\n<\/p>\n
    The next two options come with SEVERE WARNINGS<\/span>, this may cause system issues.  <\/p>\n
    Enabling the C2 audit <\/a>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.  <\/p>\n
    NOT GOOD!<\/span><\/p>\n
    SQL Server can be configured to allow ownership chaining<\/a> between specific databases or across all databases inside a single instance of SQL Server. Cross-database ownership chaining<\/a> is disabled by default, and should not be enabled unless it is specifically required.  This option can open security holes.    <\/p>\n
    NOT GOOD!<\/span>
    \n <\/p>\n
    \r\n-- Enable C2 audit (WARNING - ADDITIONAL OVERHEAD & UNCHECKED STORAGE GROWTH)\r\nEXEC sp_configure 'c2 audit mode', 1 ;\r\nRECONFIGURE WITH OVERRIDE;\r\nGO\r\n\r\n-- Enable cross db ownership chaining (WARNING - POSSIBLE SECURITY HOLES)\r\nEXEC sp_configure 'cross db ownership chaining', 1;\r\nRECONFIGURE WITH OVERRIDE;\r\nGO\r\n<\/pre>\n<\/p>\n
    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.<\/p>\n
    Next time, I will be talking about database paths that you can change via xp_regread and xp_regwrite.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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\u2019s 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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[289,296,295,287,297,284,288,12,15,293,290,266,291,292,294,265,264,28,285,286],"class_list":["post-1332","post","type-post","status-publish","format-standard","hentry","category-db-admin","tag-auditlevel","tag-c2-audit-tracing","tag-common-criteria-compliance","tag-create-credential","tag-cross-database-ownership-chaining","tag-database-admin","tag-drop-credential","tag-free-code","tag-john-f-miner-iii","tag-login-auditing","tag-loginmode","tag-reconfigure","tag-security-page","tag-server-authentication","tag-server-proxy-account","tag-sp_configure","tag-sp_xp_cmdshell_proxy_account","tag-sql-server","tag-xp_regread","tag-xp_regwrite"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1332","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1332"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1332\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1332"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1332"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1332"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}