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
111 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
  • 7
  • 6
14 Comments
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
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
Comment Utility
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:SimonAdept
Comment Utility
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
 

Author Comment

by:pcalabria
Comment Utility
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:SimonAdept
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
...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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
>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
Comment Utility
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:SimonAdept
Comment Utility
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
Comment Utility
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:
SimonAdept earned 500 total points
Comment Utility
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
Comment Utility
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:SimonAdept
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now