connection failed: SQLstate: 28000

I have SQL 2000 installed in my windows 2003 server with mixed mode authentication. When I login to the server and open the SQL server manager under my windows name everything works. And if I try to create an odbc connection from one of the client pc's or from the server itself using windows authentication still everything works. Now I opened the SQL enterprise manager and in the Security section, there is a user group called BUILTIN\Administrators, I was asked to deny access to this group in SQL. So I did that and added my windows login name in the security -> Login section. Now still if I try to open the enterprise manager and and login to sql under my windows login name it works. But if I try to create an odbc connection to the sql server either from the server itself or from the client work station I get the following error:

connection failed:
SQLstate: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'PROD\pchelin'

If I go to the security -> Login and enable BUILT\Aministrators group, everything works. But I would like to know how to disable that group and add my own windows group or login id in SQL server and connect using ODBC.

Your valuable feedback is greatly appriciated.

Hi,

I do not know which user you use to connect to SQL server except your login, and I have no idea if you are a member of built\Administrators group, but I know that if you deny Access to server for group even if user itself had rights to access server this DENY will prevent user to log into SQL Server.

I hope that it helps

JPazgier

|||

You are correct. The moment I deleted the BUILT/Administrators group it started to work. Are there any disadvantages in deleting this group?

|||

If it was windows group it is not safe to delete it. It will be good practice to create another group like SQLAdministrators and give its users rights to be admins on SQL server. The only problem can be that administrators have by default rights to SQL server so you have to just remove this rights to SQL server but do not set Deny access.

JPazgier