Server PW change prevents access to SQL DB

[VicM]

Hi,
I'm consulting with a company that uses 2 servers. The main network server uses Active Directory and is responsible for all network activities, e.g. email, remote logins, user accts, etc.

The other server is designated as a Database server and SQL Server 2005 is installed on it. However, the server is also set to use Active Directory.

I was responsible for creating a SQL back end database to support a client MSAccess front end. When I did so, I selected Windows Authentication as the login protocol. After establishing an ODBC DSN on the clients' machines everything worked well.

However, the recent termination of an employee led to the Administrator passwords being changed on both servers. When this occurred, access to the SQL back end failed with the following error msg:

Connection Failed
SQL State: 'S1000'
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver]Cannot generate SSPI Context

If I try to create a new ODBC after the PW has been changed, I get the following error msg:

Connection Failed
SQL State: 'HY000'
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver]Cannot generate SSPI Context

We had to change the Admin PW back to the original in order to access the SQL DB thru the MSACCESS front end.

I tried changing the login protocol to mixed, i.e. Windows and SQL Authentication. But that didn't work either.

Basically I'm a programmer. When it comes to in depth security issues on servers, etc., I don't have much experience.

Any insight will be greatly appreciated.
Thanks,
Vic

 

[SQLBill]

One issue I have found with changing passwords when Active Directory is involved is with active connections.

Let's say John Doe has a login DoeJ. He opens Query Analyzer, SSMS, whatever, and now has a connection to SQL Server. He then changes his Windows password. His connection to SQL Server is still using his old password, but trying to authenticate via Active Directory which has his new password and the connections will fail.

Solution. The user needs to close out ALL connections to SQL Server and then reconnect.

I hope that is the solution to your issue.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875