{"id":438,"date":"2011-08-04T14:33:41","date_gmt":"2011-08-04T19:33:41","guid":{"rendered":"http:\/\/hoolihan.net\/blog-tim\/?p=438"},"modified":"2011-08-04T14:33:41","modified_gmt":"2011-08-04T19:33:41","slug":"safely-using-an-insecure-sa-development-account","status":"publish","type":"post","link":"http:\/\/hoolihan.net\/blog-tim\/2011\/08\/04\/safely-using-an-insecure-sa-development-account\/","title":{"rendered":"Safely Using an Insecure SA Development Account"},"content":{"rendered":"

As a consultant, I’m sometimes a part of projects that do things I wouldn’t choose to do. Welcome to the real world, right? For example, on one project I worked on in the past, the connection string for an asp.net project used the “sa” account for sql server. Further, it was in the web.config file and that file was checked into source control. First, I’m not a big fan of connection strings in source control, and have already posted on an easier way<\/a>. If nothing else, use “Integrated Security=SSPI” in the connection string, and then each developer uses his local windows account to assign permissions.<\/p>\n

But I’m a consultant on this project, and I can’t dictate that change. Fine. So I just lived with it for a while. But it bothered me that anyone on the project, or ever on that project, was walking around with the same sa password, and the password policy file unchecked. Until I came up with a simple solution. <\/p>\n

Rename sa, and put the password policy back in place. And then create a new user called sa, that only has the necessary privileges on that database. If you don’t have need of your sa account for other reasons, you could just reduce the permissions in this case. But I still wanted a sql login that had permissions to administrate the database. Usually, you would do that kind of management from a domain account, but I don’t run a domain since I’m in so many different locations, and I might need to manage remotely, hence a sql login.<\/p>\n

Anyway, here are the steps:<\/p>\n

1. Rename the sa user:<\/p>\n

alter login sa with name = mynewsystemaccount<\/code><\/pre>\n
2. Restart your sql server<\/p>\n
3. Choose a new password for sa, and check the policy is enforced. I did this with management studio, but you can use t-sql if you prefer:<\/p>\n
alter login mynewsystemaccount with password='some strong new password here', \r\n      check_policy = on, check_expiration = off;<\/code><\/pre>\n
4. Create a new sa user with the previously insecure password, and only grant them the necessary permissions on the database for that development application. <\/p>\n
“sa” is still an insecure account, and any other developer can run any command they want on that development database, but they can’t administrate your whole server. There are plenty of other ways to solve this, if you have an interesting one, post in the comments below.<\/p>\n","protected":false},"excerpt":{"rendered":"
As a consultant, I’m sometimes a part of projects that do things I wouldn’t choose to do. Welcome to the real world, right? For example, on one project I worked on in the past, the connection string for an asp.net project used the “sa” account for sql server. Further, it was in the web.config file […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,34,18],"tags":[41,69,272,170],"class_list":["post-438","post","type-post","status-publish","format-standard","hentry","category-aspnet","category-microsoft","category-programming","tag-net","tag-linkedin","tag-programming","tag-sqlserver"],"_links":{"self":[{"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/posts\/438","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/comments?post=438"}],"version-history":[{"count":0,"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/posts\/438\/revisions"}],"wp:attachment":[{"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/media?parent=438"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/categories?post=438"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/tags?post=438"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}