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
112 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: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
 

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

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

895 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

15 Experts available now in Live!

Get 1:1 Help Now