Need help setting up a DSN for MS Access 2000 to work with MS SQL Server 2008 R2

Posted on 2015-02-15
Last Modified: 2015-02-16
I'm trying to connect Access 2000 to an MS SQL Server 2008 R2 table that exists on my LAN.

The machine is an XP Pro SP3 workstation.

Here's what I do:

Control Panel
Data Sources (ODBC)
System DSN
Click Add

Select: SQL Server Drive  (SQL Native Driver 10 does not appear in the list)

Name: Test1234
Desripition:Test2 This is a test
Server: MyServerName
Click Next

Select: With WIndows NT autentication
Check: Connect to SQL Server to obtain default seetings...

Click Next:

Error Message:

Connection Failed:
SQLState '28000'
SQL Server Error: 18452
[Microsoft][ODBC SQL Server Driver][SQL Server]Login Failed.  The login is from an untrused domain and can not be used with WIndows authentication.

What's wrong?
Question by:pcalabria
  • 7
  • 6
LVL 18

Expert Comment

ID: 40611143
Q: What's wrong?
A: The login is from an untrused domain and can not be used with WIndows authentication.

OK, the SQL server is on your LAN, but is it a single Active Directory domain or multiple AD domains with trust relationships established?

Fastest resolution is probably to use SQL username/password if you have that or can get it set up for you.

Author Comment

ID: 40611157
We do have two Active Domains and sometime we get a problem like this if we are logged on for too many hours.  I rebooted and now I'm getting a different error.  This is the error I get consistently.

connection failed
SQLState HY000
SQL Server Error: 0
Cannot generate SPSI Context

The SQLState has changed a couple of tiems, but the message Cannot generate SPSI Context is there every time.
LVL 18

Expert Comment

ID: 40611199
I can only suggest you follow the diagnostic steps in this Microsoft Support article. Another expert may be able to provide more targeted advice.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Author Comment

ID: 40611319

I've very confused because I currently have an Access front end talking to the SQL fine, but I need to rebuild it, so I copied all the tables to a new instance, and the new instance will not connect!

Any other thoughts would be appreciated.  I'll check the article.
LVL 18

Expert Comment

ID: 40611323
Both old and new front ends on the same PC? I assume not, if you've had to create a new DSN. I'd suggest you go back to the machine or DSN that works and check the properties of the DSN carefully.

If your new instance of the frontend IS on the same machine, please test it with the DSN that works with the old frontend.
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40611327
...I currently have an Access front end talking to the SQL fine, but I need to rebuild it,

In access, in database window, right click on one of the linked tables and select Linked Table Manager. This will give you the list of all linked tables. Choose all to refresh the link.


Author Comment

ID: 40611373
when I do this I get the "Cannot generate SPSI Context error".
I imported the tables from another access database file that was working fine.
In the other database, the table links work find.  But after importing them into a new mdb file, they give me the above message.
LVL 18

Expert Comment

ID: 40611764
>I imported the tables from another access database file that was working fine.
It sounds like you have imported the linked MSSQL tables from one access db to another. Either do what eghtebas suggested and use the linked table manager to try and and refresh or re-target the links, or if that fails, delete the linked tables and re-create them using the Data/Import/From SQL Server option.

If you hover the mouse over the linked table in your old, working db, you will see the linked table definition, including the DSN it used and the name of the object it links to.

Author Comment

ID: 40611931
Right clicking does not show me the linked table definition.  Neither does hovering over the linked table.
(I'm using Access 2000)

I can still get into the corrupt database.
Is there some way I can tell:

What type of DSN the existing linked tables are using (System, User, File)
Also the linked table defintion?
LVL 18

Expert Comment

ID: 40611952
I use similar code to this, though I just Googled this from and added the line to show the connect string.

I'd suggest you put it in a new module in your old database and step through it.

Sub GetLinkedTableInfo
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If Len(tdfCurr.Connect) > 0 Then
Debug.Print tdfCurr.connect
End If
Next tdfCurr
Set dbCurr = Nothing
end sub

Open in new window

You should get a line like this for the connect string

ODBC; DATABASE=database; UID=user; PWD=password; DSN= datasourcename; [LOGINTIMEOUT=seconds;]

I'd suggest you also look at ODBC administrator to see what user/file/system DSNs are configured on the PC. There may only be one possible choice there.

Author Comment

ID: 40612019
Thanks!  I've been up all night working on this and finally I feel like I'm making progress.

Here is my string (with only the password changed for this posting)

ODBC;DRIVER=SQL Server;SERVER=SQLSERVERLITE;UID=DistiMasterUser;PWD=12345678;APP=Microsoft® Access;WSID=EXECUTIVE;DATABASE=Inventory_CompPricingSQL;Network=DBMSSOCN;Address=,1433

Now how would I go about saving this connection string  in the new database?
LVL 18

Accepted Solution

Simon earned 500 total points
ID: 40612368
Personally, I would create a SQL Server system DSN on your PC using that IP address and user credentials (in ODBC administrator).

For a DSN-less connections, you might like to look at this Support.Microsoft link, or search EE, as I think the topic has been covered in previously answered questions and/or articles.

Out of interest you might want to try running the code I posted in your NEW database to see how the connection strings compare to the old database. It might just be that the password was excluded when you copied the linked table objects across. If so, you might be able to use linked table manager to rebuild them, by choosing 'always prompt for new data source' and then providing your password at the appropriate stage of the process.

Author Closing Comment

ID: 40612382
Thanks Simon,

I actually solved this one myself, but you have been very helpful.
I've saved your code for future use.

Here is the solution.  Almost too easy:

Step 1, Create a system DSN using SQL Authentication.
Step 2, Open access, create an odbc connection, enter the password, and click okay.  When the dialog box showing all the tables you have access to appear, select the tables that you want, and then notice that on the right side of the screen there is a save password box.  CHECK IT!

Done.   You can exit and then reload, or deploy to other users on the LAN, and you are not asked for a password.  No code in autoexec or onOpen events to cause development issues.

NOTE: You can NOT save the password using the Linked Table Manager, regardless of what you do.  That was the distraction that kept me up all night!
LVL 18

Expert Comment

ID: 40612398
Cool. Glad you're sorted.

One word of caution:
>You can exit and then reload, or deploy to other users on the LAN, and you are not asked for a password.

At my workplace a previous developer had set up all connections like this, but on occasion the linked tables DID FAIL and require re-linking (thus requiring intevention from someone who knew the password).

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question