Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2015-02-15
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 34

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 http://www.pcreview.co.uk/forums/do-you-get-linked-table-property-using-vba-t3550775.html 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.name
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 1500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

610 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