Solved

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

Posted on 2015-02-15
14
129 Views
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?
0
Comment
Question by:pcalabria
[X]
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
14 Comments
 
LVL 18

Expert Comment

by:Simon
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.
0
 

Author Comment

by:pcalabria
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.
0
 
LVL 18

Expert Comment

by:Simon
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.
0
Independent Software Vendors: 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!

 

Author Comment

by:pcalabria
ID: 40611319
Okay.  

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.
0
 
LVL 18

Expert Comment

by:Simon
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.
0
 
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.

Mike
0
 

Author Comment

by:pcalabria
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.
0
 
LVL 18

Expert Comment

by:Simon
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.
0
 

Author Comment

by:pcalabria
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?
0
 
LVL 18

Expert Comment

by:Simon
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
      

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

Author Comment

by:pcalabria
ID: 40612019
Simon,
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=192.168.50.12,1433

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

Accepted Solution

by:
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.
0
 

Author Closing Comment

by:pcalabria
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!
0
 
LVL 18

Expert Comment

by:Simon
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).
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

738 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