SQL Server Error 18456

[rsbutterfly16]

I'm running an Access 2000 Database on Windows 2000. The Access Database links to SQL Server 2000 tables. The error occurs when the users login with their windows login (my sql server is mixed authentication) and open the database to open the tables or forms. The connection will fail with the following:

Connection failed:
SQLState '28000'
SQL Server Error 18456
[Microsoft][ODBC SQLServer Driver][SQL Server]Login failed for domain/'user"






I used in the Data Source set up and tested the connection and I have as WindowsNt authentication using the network login ID from (Access) All these users are in added to sql server as users for these database using windows authentication.

The users belong to the group in sql server "users" , when i add the "administrator" group to their login then they can login without anyproblems but for obvious security reasons i have to take them out of the administrator's group.

things i already checked:

sql server is mixed authentication
odbc conection to sql server database is windows authentication (from access i used the link table option to sql server and created a conection to sql server)
user has read and write access to the sql server database.
user is part of the user group in sql server




Is there anything else to check for?

Thanks in advance.

 

[pdx_man]

What databases does the user group have access to? What is the permissions set for that group? Does it have SELECT permissions to the tables in question?

 

[rsbutterfly16]

the users are part of the users group in sql server and i added them in the sql server database with db read rights and db write rights.

 

[pdx_man]

Are there network groups in the DB where the user may already be a member of? Are there any DENY permissions set for them.

Also, just to make sure I am clear ...

You have individual NT user accounts with access granted to a DB with database role membership public, db_datareader & db_datawriter.

You have confirmed the ODBC DSN utilized in the linked server from Access is utilizing NT authentication without a default database selected.
(If you do not have access to the default DB, it won't matter what the permissions are set to, and you will get this error)

 

[rsbutterfly16]

thanks for replying, to answer your questions:

Q. Are there network groups in the DB where the user may already be a member of? Are there any DENY permissions set for them.

A. do you mean in the access level? as of now they just belong to the everyone's group , i went to access, right click , properties, and went to the security tab.


Q. You have individual NT user accounts with access granted to a DB with database role membership public, db_datareader & db_datawriter.

A. Yes.

Q. You have confirmed the ODBC DSN utilized in the linked server from Access is utilizing NT authentication without a default database selected.
(If you do not have access to the default DB, it won't matter what the permissions are set to, and you will get this error)

A. My database has its own ODBC DSN conection pointing as a default to the SQL Server dAtabase where its tables are.

Another thing that might not make sense... i logged in my desktop with that user credentials (windows) and i was able to connect fine(only as a user) but when i go to the user's desktop in order to connect to the linked tables of the access databasee i have to add him as administrator in sql server... if not i get that error message above...

I am running out of ideas.. help.

 

[pdx_man]

In response to you first answer, no, the NT level. You can have a network group defined in Active Directory with members. Then you just have the that group as a user in SQL Server.

As far as being able to log in from your machine but not the users, I would first delete and create the DSN again. It may have gone sideways on you. If that doesn't work, check the version of MDAC on the two machines. The user's machine probably needs updating. Do a Google search on MDAC version checker. Another quick way to check is look at the version of the SQL driver when creating a new DSN.

 

[rsbutterfly16]

thank you so much, i will do that, oh and to answer your question corrently this users are part of the domain/users group (default) in active directory

 

[rsbutterfly16]

hi, its me again, still my problem has not been resolved... my sql server 2000 sp is mdac version 2.80.1022 and the user's desktop is version mdac 2.8 sp1 on windows XPS2. she is a member of the user's group with her windows login domain\user , i added her to the database wiht db_reader and db_writer rights and i also add her as only user (without the domain) . my sql server is with mixed authentication. in active directory she is part of the built in user's group..... can you tell me what else to check please....

 

[pdx_man]

Hmmm ... try creating a SQL account and she if she can log in that way.

 

[rsbutterfly16]

nope not either. I even deleted and recreated the account again. I added a sql account and a windows account and none of them work.

 

[pdx_man]

When trying to connect through a SQL account, you will have to change your ODBC DSN. You don't want it to be using NT authentication.

in active directory she is part of the built in user's group
What is the name of that group?

Get rid of any individual SQL Accounts or Windows accounts for that individual user.

Ensure that group is listed under the Security, Logins on that server. It should be listed as domain\GroupName.

Ensure the user is a member of this group. In QA run:
exec master..xp_logininfo 'domain\GroupName' ,members

Open up that login and ensure the Security access is set to Grant Access. Have the Default Database set to Master. On the Database Access tab, make sure it has db_datareader access to the database in question.

Delete your ODBC DSN
Create a new DSN with a different name using NT Authentication on that users machine. Set the Default database to the database in question. Test the connection.

Delete the linked table in your Access database.
Add a new linked table using your new DSN.

If this doesn't work, well ... it has to be an MDAC issue.

 

[rsbutterfly16]

thank you for responding!!!

in active directory she is part of the built in user's group
What is the name of that group? domain users local/users

ok so just to make sure i do this part right...

Get rid of any individual SQL Accounts or Windows accounts for that individual user.

I go to security , logins, and just delete this user login (right click, delete) or go to computer management and go to users and just right click the user name and delete...

thank you for taking interest in my neverending issue.. i really apreciate it.

 

[pdx_man]

No prob.

In AD, you can create other network groups, like, say, Finance. So then, you would have domain\Finance as a group. This is the item that needs to added as a SQL Server user. Very rarely would you add a domain\AllUsers group to SQL. Only for a very generic role. You would be better off having all of the employees in their specific groups and then add the groups to SQL.

This has nothing to do with Computer Management. Stay away from there. Remove the logins from SQL Server.

Are you a network person at your company? If not, work with one of them to get this setup. Tell them all of the users that need to be added to the domain\Finance (eg) group. They should be able to do this in about 1 minute.

Then, add the domain\Finance user to SQL and set it up as I outlined above.

 

[rsbutterfly16]

hi, ok so i did what you said i have a couple of questions:

the domain/group name login has db_datareader rights to the database on question and default database is master. (question: on the database tab, should master database also be checked or only the MB that he needs access to?)


I have not changed any of the settings in the computer management in sql server, but i want to make sure is correct, under local users and groups, the user name is under users and he is a member of the "users" group.

ok i executed your sp and yes he is part of the domain\group name.

so i logged in as administrator to his computers and deleted the odbc dsn and created a new one (under machine dsns) , made sure it Nt authentication and set the default database tested the conection and it works. i relinked the tables in the database with this new dsn and it works fine. i logged off and logged in with the users credentials. when i try to use that table then i get a message saying 'ODBC-conection to DATA MB conection failed'.


I checked her MDAC version and is 2.8 just like mines. I am really going nuts with this issue.. did i do something wrong?

 

[pdx_man]

You do not need to have the master DB checked in the Database Access tab.

Can you create a DSN when she is logged in? Do this and re-link the tables under her login.

If she logs in to your machine with her credentials, can she connect? (make sure you have a DSN of the same name)

I would have to remote in to her machine to really see what is going on. Do you have a network person procfient in SQL installations?

 

[rsbutterfly16]

when she logs in my deskstop with her credentials she could use the tables fine... and create a dsn and everything. I created a new dsn and relinked the table however still when she goes to her desktop and she logs in she can't conect to that dsn.

also, when i go to her desktop , i log in with her credentials, if i try to create a dsn it does the same error message.


could this have to do anything with her registry keys?

 

[pdx_man]

I can't think of anything in the registry that would cause this.

So, under her login, on her machine, you can create a DSN and within that DSN, you can test the connectivity fine?

 

[rsbutterfly16]

nope, on my machine, with her credentials everything is perfect, but when i go on her machine i get that error message...

 

[pdx_man]

OK, so your user machines are locked down. What operating system are you using? You said you created a machine DSN, on my computer, all I have is User, System & File DSNs. You need to have a System DSN, which I am guessing is the same as machine, but ... If you have a system DSN, she should be able to see it.

Are other users able to connect? Perhaps her machine is locked down too tightly.

Anyway you look at it, this is where your problem lies.

 

[rsbutterfly16]

she sees the system dsn, however when i try to test the conection it says that sql error message 18456....('null')" , only when i run as administrator it can run ok.

what do you mean with machine is locked down too tightly?