Safe to reset sa password?

Question

Safe to reset sa password?

dan-572483

SSCoach

Points: 15870
More actions
November 25, 2013 at 11:31 am

#98948

I have been asked to resolve an issue where the SQL sa password is - well, a password not considered "best practice" for security. This instance SQL 2000 hosting Sharepoint 2003 on Windows 2003 Server.
When I run sp_who2, all I see under sa appear to be internal server processes, Log Writer, Lazy Writer, Task Manager, etc, with no HostName or ProgramName. As far as I can tell looking at the Sharepoint Central Administration site, Windows Authentication is used.
What is the best way to identify anything that might be configued to use the sa login & password that needs to be changed, and when it is safe to reset the sa password without breaking anything?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 1

Hi Dan,

First I would suggest that you create unique SQL accounts (or even NT accounts) for systems to access your server. Most things that are connecting to your server does not need sysadmin role. So, you will need to audit who is accessing your system using SA and then create a new account and update those to use that. In order to audit who is connecting to your server I would use a server-side trace that is capturing the host machine and database where the login is SA.

It has been awhile since I had to deal with 7.0 or 2000, but I don't believe that you can disable the SA account (which is what I do on my servers 2005+)

You can ignore internal processes.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Nadrek SSC-Insane Points: 20039 More actions · Answer 2

Make sure you have a separate sysadmin account you use.

Graphically, via SSMS:

Server Properties (right-click on the servername in Object Explorer, Properties), Security, then audit both failed and successful logins.

Restart the SQL Server service (during downtime)!

Or something like (not tested on 2000, sorry!)

EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel',

REG_DWORD, 3

Watch your log to see if 'sa' is ever showing up. On 2000, it won't tell you the incoming IP address, though, so you'll have to use other statements to figure out what 'sa' is being used for.