{"id":2007,"date":"2012-05-23T17:18:57","date_gmt":"2012-05-23T17:18:57","guid":{"rendered":"http:\/\/craftydba.com\/?p=2007"},"modified":"2017-10-12T00:45:42","modified_gmt":"2017-10-12T00:45:42","slug":"protecting-the-sa-account","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=2007","title":{"rendered":"Protecting the [sa] Account"},"content":{"rendered":"<p>During the installation of SQL Server, you are prompted to select an <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms144284.aspx\">authentication mode<\/a>.  <\/p>\n<p>Regardless of the choice, a [sa] account will be created.  If you are using Windows Authentication, the account will be be left in a disabled state.  On the other hand, if you are using Mixed Mode Authentication, you are asked for a password and the [sa] account will be left in an enabled state.<\/p>\n<p>Because the sa account is well known and often targeted by malicious users, do not enable the [sa] account unless your application requires it.   Never set a blank or weak password for this account.<\/p>\n<p>Some organizations try to protect the Local Windows Administrator account or the SQL server admin account by obscuring the name of the account.  Below is a TSQL snippet that will disable and rename the [sa] account.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"tsql - rename sa account\">\r\n-- Disable the account\r\nALTER LOGIN [sa] DISABLE;  \r\n\r\n-- Rename the account\r\nALTER LOGIN [sa] WITH NAME = [sys-adm];\r\n<\/pre>\n<\/p>\n<p>Keep in mind that this action might causes issues with software that is hard coded.  For instance, the following <a href=\"http:\/\/support.microsoft.com\/kb\/968829\">support ticket<\/a> at Microsoft shows that the upgrade script from SQL Server 2005 to 2008 fails due to this issue.<\/p>\n<p>Instead, Microsoft should have searched by looking for the SID instead of the NAME.  The TSQL snippet below searches for the sa account by SID.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"system view - sys.server_principles\">\r\n-- Search for the account\r\nSELECT p.name \r\nFROM sys.server_principals P \r\nWHERE P.type = 'S' AND P.name = SUSER_SNAME(0x01) \r\n<\/pre>\n<\/p>\n<p>In short, unless you need to use the [sa] account, leave it in a disabled state.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>During the installation of SQL Server, you are prompted to select an authentication mode. Regardless of the choice, a [sa] account will be created. If you are using Windows Authentication, the account will be be left in a disabled state. On the other hand, if you are using Mixed Mode Authentication, you are asked for a password and the [sa] account will be left in an enabled state. Because the sa account is well known and often targeted by malicious users, do not enable the [sa] account unless your application&hellip;<\/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":[57,424,12,15,425,423,426,427,29],"class_list":["post-2007","post","type-post","status-publish","format-standard","hentry","category-db-admin","tag-alter-login","tag-disable","tag-free-code","tag-john-f-miner-iii","tag-rename","tag-sa-account","tag-search-by-sid","tag-suser_sname","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2007","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=2007"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2007\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2007"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2007"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2007"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}