No, you can’t use the sa login. Not yours.

As previously posted, I’m in the process of locking down the MS SQL 2005 server that I am in charge of maintaining. The built-in admin account group was no longer in the sysadmin group so it was time to move to the next hole. The ’sa’ account.

The ’sa’ account is the default admin login that has full control of the MS SQL server. If the server is only going to use Windows Authentication, a random password can be created and forgotten. A Windows domain user would be assigned to the sysadmin SQL server group because ’sa’ could not be used (SQL logins disabled). This isn’t the case for my server. I had to set it up for mixed mode authentication. Mix mode means that Windows Authentication or SQL logins can be used. The reason I had to use mix mode is due to some software that we use that does not support WA for it’s SQL connection.

I also mentioned in the previous post that there was a certain network admin that was abusing the “domain admins are automatically MS SQL sysadmin’s” set up. Let’s call him “Craig”. I didn’t mention that Craig was also abusing the fact that he knew the ’sa’ password. I knew he had used it to connect to the server using SQL Server Management Studio but that was only one place he had used it. I didn’t know that until after I changed the ’sa’ password Friday morning.

Ten minutes after I changed the password, I noticed several ’sa’ user login failures in the SQL logs. The IP address being logged was assigned to the server running Trend Micro Control Manager (TMCM). Craig is in charge of that server. I asked him why TMCM was trying to connect to the SQL server using ’sa’. He said, “That’s the account it uses to connect to the database.” After the initial feelings of blinding rage left, I told him it isn’t connecting now. He tried to pull up the web GUI and it failed. He asked, “What did you do!?! Did you change the password?” I told him I had changed it a few minutes ago.

Craig almost came unglued. He started going on about how that connection was needed because TMCM controlled deployment of our antivirus software and was going to control some new Trend Micro software that was coming in. He also said, “You can’t just go changing passwords!” I informed him that:

You are never supposed to use the ’sa’ account for anything except server administration.
I had set up a SQL login for TMCM (3.5 doesn’t support Windows Authentication) as a DBO for the TMCM database.
You are never supposed to use the ’sa’ account for anything except server administration. (I repeated it just to be sure he heard it)
I can change the ’sa’ password every 30 seconds if I think it’s necessary.

The last point was actually made clear to me by my supervisor. I had told my supervisor about what I was doing in securing the SQL server (including changing the ’sa’ password) earlier in the week. His response boiled down to: “It’s your server. Do what you think is necessary to lock it down.”

Back to Craig. He said that the ’sa’ account was the default and there wasn’t any way to change it without reinstalling. I did a Google search and found a Trend Micro KB article showing how to change the SQL login information. The article was written for TMCM 3.0 and for changing the database server but it still fit. I only needed to change the SQL username & password so I was able to skip some of the steps. I copied the files that needed to be edited from the server, made the required changes and emailed them to the network admin. The email included a link to the Trend KB article. He was copied the files to the server and restarted the Trend Micro services. When I looked at the SQL Activity Monitor, I was able to confirm that TMCM was using the SQL login I created and not ’sa’.

Now that the two largest holes are closed, it’s time to move on to the smaller security issues.

When science discovers the center of the universe, a lot of people will be disappointed to find they are not it. – Bernard Baily

04.Aug.07 Microsoft SQL, Security