SQL 2000 Server Error: 18456

[purefreshair]

Hello,

I have a question regarding Windows Authentication. I have an instance of SQL 2000 server (SP3) running on a server that has Windows 2003 server, standard edition on it. The server is part of the same domain name in our active directory. In the Security tab settings of Enterprise manager, it is set as mixed mode (Windows authentication and SQL server authentication). When I attempt to create an ODBC connection from my PC (Windows XP pro/SP2) and select the authenticity of the login ID of "With Windows NT authentication using the network login ID", I get the "Connection Failed: SQLState: '28000' SQLserver Error:18456.......".

If I create myself as a user in the SQL server's security folder/logins and specify windows authentication however, my ODBC connection works.

OK, I may have just answered my question here but, does a user (or user group) have to be defined within the SQL server's login's in order for window's authentication to work? I was under the thinking that if a user had a valid login and password to the domain, that an ODBC connection using "windows authentication" to the SQL server/database would work. Is it due to the "mixed mode" setting on Enterprise manager?

 

[SQLBill]

No matter what, a user MUST have a SQL Server login. That login can be set for either SQL Server Authentication - in which case a password must be created, or Windows Authentication - in which case the login must be the same used to log into Windows.

It's like anything else, just because you have a valid login and password to a domain doesn't mean you can access ANY drive/folder/file on the domain. You must have permissions set on that object to be able to access it. The same goes for SQL Server.

-SQLBill

Posting advice: FAQ481-4875

 

[purefreshair]

Got it. Thanks.