SQL Server Error 18456

Question

SQL Server Error 18456

jessica-321853

SSCrazy

Points: 2207
More actions
July 3, 2006 at 12:37 pm

#103196

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
user 'xy'.
I used in the Data Source set up and tested the connection and I have as WindowsNt authentication using the network login ID. 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.
Is there anything else to check for?
Thanks in advance.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

Have you granted the "users" group any acess to specific databases, tables, veiws etc? will they only need read, or will them need CRUD access?

To help us help you read this[/url]For better help with performance problems please read this[/url]

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

Or this may have some insights as well...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=190160

To help us help you read this[/url]For better help with performance problems please read this[/url]

jessica-321853 SSCrazy Points: 2207 More actions · Answer 3

thanks for your response, they all have the same access in the database , data read, data write. any thing else i could check?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

This looks like the linked tables were connected using SQL Server authentication, not Windows authentication... otherwise you'd see Login failed for Domain\User, or are you leaving out the domain?

K. Brian Kelley
@kbriankelley

jessica-321853 SSCrazy Points: 2207 More actions · Answer 5

yes i checked my odbc conection and it is set up with windows nt authentication, in the error message: it says

win2k3-3/user .... win2k3-3 is the sql server name.

jessica-321853 SSCrazy Points: 2207 More actions · Answer 6

what i did to the access database was to link the external sql server table by going to file , get external data, link tables, data sources, odbc, new dsn name, driver= sql server, authenticiy: with windows nt authentication using the network login ID, and i changed the default database to the database where my tables are.

Please let me know if i did something wrong or if i need to change anything else..

as i said before, everything works correctly as long as you are a member of the administrators group.

thank you .l

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 7

Can you link tables when they are logged in?

Perhaps you can use dsn-less connections instead of using odbc-dsn's

Windows authentication connectionstring:

ODBC;DRIVER=SQL Server;

SERVER=your_server_name;DATABASE=your_database_name;Trusted_Connection=Yes

Sql server authentication connectionstring:

ODBC;DRIVER=SQL Server;SERVER=your_server_name;DATABASE=your_database_name;

UID=sql_server_login_name;pwd=your_passwd

jessica-321853 SSCrazy Points: 2207 More actions · Answer 8

thanks Jo, can you please tell me how to set up this dsn-less conection. what i usually do is from access go to the table and relink to an odbc database and from there create the odbc conection. but your answer sounds right, it has to do something with the conection.. if you can please explain how to set this up i would extremely apreciate it.

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 9

This should do it

Dim db As Database

Dim rs As Recordset

Dim sSql As String

Dim tdf As TableDef

Dim tdf_change As TableDef

Set db = CurrentDb

For Each tdf In db.TableDefs

Set tdf_change = db.TableDefs(tdf.Name)

tdf_change.Connect = newconnectionstring tdf_change.RefreshLink

End If

I've only used dao in Access. Not sure how to access linked tables in ADO

jessica-321853 SSCrazy Points: 2207 More actions · Answer 10

thank you so much, one last question before i try your answer... can it be something to do with the workstation ??? when i try from a different deskstop with the same credentials and only being a user in sql server (how i wanted to be) I COULD CONNECT OK, ...

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 11

I'll be off for 2 weeks. Since you can login using a sql user the problem is with the windows validation.

I've noticed that changing ODBC-settings in normal user mode weren't saved. Only when you have administrator permissions, you can change the odbc-settings permanently.

jessica-321853 SSCrazy Points: 2207 More actions · Answer 12

ok thanks for your help, just in case, i meant i used her windows credential in another desktop and it worked fine. not sql server credentials

jessica-321853 SSCrazy Points: 2207 More actions · Answer 13

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....