| Author |
Topic |
|
SMerrill
Posting Yak Master
206 Posts |
 Posted - 2003-07-28 : 19:26:31
|
| I changed the "SA" password in SQL 7 from blank to something, on the production server.I have changed every process I can think of, but now my attention is directed toward the nightly SQL Jobs. The time in the SQL Server log file entry is leading me toward that thought. The only problem is that the source is simply "logon" and the error is "Login failed for user 'sa'."Now to the Jobs: There are jobs scheduled to run at the times specified. I noticed that a Job has an Owner, which is a username, one of which is "sa". How do I change the password it is using?BTW, the job doesn't seem to think it is failing.--Shaun MerrillSeattle, WA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-28 : 19:37:45
|
| The job isn't failing because a job does not use a password, so there isn't anywhere to change it. So something else is using sa with a bad password. To see what is trying to logon with sa and the incorrect password, you should run SQL Profiler. I would add hostname to the trace.Tara |
 |
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2003-07-29 : 13:44:35
|
| Thanx Tara, but I can't quite find "hostname" in the trace properties dialog box. Can you tell me how, please?--Shaun MerrillSeattle, WA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-29 : 13:52:03
|
| Open up SQL Profiler. Click New, then trace. Type the name of the server that you want to trace, enter the appropriate credentials and hit OK. Give the trace an appropriate name, I typically send the results to a table so that I can later perform T-SQL on it, but do whatever you want on this screen. Go to events. Security audit is part of the default, which are really the only events that you care about. So you can remove the others or add other events if you choose. Go to data columns. On the left side, select hostname, then click add. You can then go to filters to filter the trace, but for what you are doing I wouldn't filter anything. Then click run. Leave the trace running until you see the culprit. Then click the red square that will stop the trace.Tara |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2003-07-29 : 14:19:19
|
| I had done all that, but it wasn't on the left side.Microsoft SQL Server Profiler Version 7.00.694--Shaun MerrillSeattle, WA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-29 : 14:24:48
|
| Ah, SQL 7.0. Do you have SQL 2k tools installed anywhere? When I connect to a SQL 7.0 machine using SQL Profiler 2000, I am able to select hostname as a data column.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-29 : 14:32:56
|
| Forgot to mention that you need to add Audit Login Failed from the Security Audit in order to catch this. I just ran a test with a SQL 7.0. machine using SQL Profiler 2000 and did not show the hostname for the Audit Login failed event. I am able to see the hostname for other events occurring but not for the failure event. It does show the ApplicationName though, which will probably help you out.Tara |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2003-07-29 : 14:34:16
|
| Y'know Tara, it doesn't matter. all the processes run from the same host anyway. I think that hostname issue is a "oie sauvage" (fr).However, my log entries declaring the inability to log in are coincident to the timing of the daily jobs.--Shaun MerrillSeattle, WA |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2003-07-29 : 15:46:55
|
| Ah...I found a clearer message in the log files:Message: The job failed. Unable to determine if the owner (sa) of job "Import IVR" has server access (reason: Unable to connect to server - check SQL Server and SQL Server Agent errorlogs).Any help?~ Shaun MerrillSeattle, WA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-29 : 15:51:13
|
| That seems very strange. Check to see if the sa account is a member of the system administrators server role. Are you able to connect with the sa account in Query Analyzer?Tara |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2003-07-29 : 15:56:36
|
| Yes, sa is a member of the role. Yes, I can log in via query analyzer with the new password but remember I told you in the first post that I just changed the sa user password from a blank password to close the security hole.~ Shaun MerrillSeattle, WA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-29 : 15:59:56
|
| Have you tried recreating the jobs? How about changing the owner of the job to another account that has system administrator privileges? The account that the SQLSERVERAGENT logs on as, is it an administrator on the machine? Was the BUILTIN\Administrators group removed from the SQL Server or from the system administrators role?Tara |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2003-07-29 : 16:00:04
|
| Ah. I may have found it. I did not realize that the SQL Server Agent had to log in as SA. I changed the password that it logs in with.~ Shaun MerrillSeattle, WA |
|
|
|
|
Sql7 SA Pwd changed