problem logging into mssql 2005

[SaveSheep]

I have SQL 2005 installed on a 64bit windows 2003 advanced server system. I just recently changed the password of the administrator account (which has always had access to sql) and now I can't login to sql.

Well, I can. It allows me to view everything, but when I right click on my db server in the sql management studio, and choose properties, it tells me my password is wrong. However, when I use windows authentication..it doesn't allow me to type a password. Is it saved somewhere?

Thanks!

[chazzy]

When you use windows authentication, it just uses your current user id.

[SaveSheep]

thats what i thought..but how come its telling me i have the wrong password?

[chazzy]

i dunno, where do you see it?

[SaveSheep]

okay,

when i first open sql management studio, it asks me to login. yes?

I choose windows authentication, and it blocks out the user/pass but puts in southeast\administrator as the user. ***nothing goes in for pw***

I click login, and it successfully logs in. Everything is good. I can browse the databases, look/edit users, etc etc.

BUT when I right click on the database server in the tree (SERVER2 is its name) and goto properties..it generates about 5 errors, the final one being 'login failed: password is invalid'. I have to view the properties of server2 using the administrator account so I can disable successful audits from logging in the event viewer.

Make sense now? Its kinda tough to explain.

thanks again

[chazzy]

nope, makes perfect sense.

is "SERVER2" setup for windows authentication? does it know that windows auth users have admin rights?

[SaveSheep]

i assume so? i actually didn't set it up myself. How would i go about checking?


i can view properties of SERVER2 with another account that has admin access to it. I also changed the audit rules to failed logins ONLY...it got rid of a lot of audits, but it still shows all audits of administrator successful/failed. So I'm assuming the only way I can adjust the administrator from logging successful audits is by signing in under that name and disabling them..is that correct? But if the administrator doesn't have access as you say, then why would it audit? Does SQL require it to audit anyways - access or not?


Thanks a lot, I'm glad you understand my problem. I've had a hard time explaining it to other people.

[chazzy]

maybe start by looking at this, from our sister site

http://www.codeguru.com/csharp/.net/...cle.php/c7725/

importantly, look at this section

To configure SQL Server for Windows integrated security:

1. From the Windows Start menu, choose Microsoft SQL Server, and then choose Enterprise Manager.
2. Open the node for the server and expand the node for the database you want to give users permissions for.
3. Right-click the Users node and choose New Database User.
4. In the Database User Properties dialog box, enter domain\username in the Login name box, and then click OK. Alternatively, configure the SQL Server to allow all domain users to access the database.

Probably you need to go in there, give your user admin rights, from the other admin's account. should be good.

as for auditing, no clue. never paid attention :/

[mattyblah]

nope, makes perfect sense.

is "SERVER2" setup for windows authentication? does it know that windows auth users have admin rights?

You can't setup SQL Server to "not" accept windows authentication. Your two options are "windows only" or "sql server and windows". Are you on a separate domain? If your username/password match over separate domains sql server will authenticate you. but if you change your password on one domain and not on the other you will not be able to authenticate.

[chazzy]

You can't setup SQL Server to "not" accept windows authentication.

I could have sworn that this was a feature of the Surface Area Configuration tool.

[mattyblah]

I'm definitely not a sql server guru but it wouldn't make sense to disallow windows authentication, considering that microsoft recommends windows authentication to sql server.