Changing the sa password
-
Hi, I am an Oracle DBA who has been handed 50 unmanaged Sql Server Instances and I am being asked to secure and administer them. I see that on many of the servers the sa password is blank, of course I want to change all the passwords to be secured, but can it cause any services or application connections to fail? Thank you in advance.
-
If an application is connecting to the server using the sa account (definitely up there for a worst practice), then yes, changing the password can result in the application no longer working. What you can do is stand up SQL Profiler and look at the existing connections to see what user accounts are being used.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
That is a great idea, thank you!
-
nah, it's a horrible idea using sa for connections.
change the pwds, pick up your phone and chastise the users. Make them change the account.
Steve Jones
-
Agreed. Using the sa account is a very BAD idea. However, certain apps sometimes require it because of the way they were developed.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
I'll agree to. And I just wouldnt tolerate an app from a 3rd party that used SA either.
Andy
-
Thank you all very much for your kind and prompt input, I really appreciate it!
-
I am running into the same problem rdann is having. all of a sudden the company wants to secure the sa login.
Brian you suggested using SQL Profiler. I have never uesd it. I was always afraid to use profiler - I have heard it is a resource hog. And then I thought it would be useless to run during off hours because no one is on it.
Can U lead me to a direction for information on what to ask profiles to look for to get the connection information. Will running this use tons of resources!
-
Profiler can be a resource hog if you're attempting to look at too much but it's intended to be run during "production" so a DBA can get a good look at what's going on.
If you're just trying to look at login and connection information, here are some events to monitor under SQL Server 2000's version:
- Sessions | ExistingConnection
- Security Audit | Audit Login
- Security Audit | Audit Logout
These are part of the default template, BTW. So you shouldn't need to add them.
If you're wanting to see login failures, add
- Security Audit | Audit Login Failed
This should get you started.
This would be a good place to plug my e-book as well, though I'm not very good at such things. The e-Book, Start to Finish Guide to SQL Server Performance Monitoring, shows you how to use both PerfMon/System Monitor and Profiler to do Performance Monitoring on a SQL Server system. A sneak peek can be found here (Excerpt 3 shows Profiler being used to help find locking issues):
http://www.sqlservercentral.com/columnists/bkelley/perfmonexcerpt.asp
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Decent plug for a decent book!
Profiler is not nearly as bad in SQL2K. In SQL7 it could load the box pretty heavily. Definitely start with just a few events and see what you're getting. Often a mistake to try to filter too heavily, better to load into a table and filter using a tool that you know - QA!
Andy
-
Thanks Brian - I just purchased your book. I'll use it to play on our test servers. And just think If I have any questions I can go right to the author.
Tell me that's not a plus!! -
I agree that when the author is available to respond to questions, that's always great. I think I had a question on DTS and was able to send to Brian Knight directly. Got an answer back within a couple of hours. Did the same with Rob Viera. That makes a huge difference, IMHO.
The other great thing is a community like SQL Server. I won't pretend like I'm going to know all the answers. Thankfully, there are a lot of great people here who have a wide range of experiences. Chances are someone knows the answer when I don't. So as an author, it's a great built-in support group. After all, look at all the people who contributed to the discussion on this topic alone!
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Tell me that's not a plus!! 
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply