Access 2k FE & ODBC to 2008R2 BE

[Grunners]

Hi there,

I have a very old Access 2000 frontend that sits on a number of XPSP3 client machines. We've recently decided to upgrade the backend from SQL 2000 to SQL 2008 R2. That bit was Sage's fault!

I'm using a test domain with users all set up and three client PC's with the 2000 frontend on. I've also added the domain users onto the SQL 2008 named instance.

With the old set up I'm used to creating the ODBC connections on the clients and then linking to tables on the SQL server through the Access clients.

Needless to say I can't create ODBC connections to the new server. I can telnet it on 1433, but ODBC creation fails with the following error:

******************
Connection failed.
SQL State 28000
SQL Server Error 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user testdomain\pete
******************

Should I even be using ODBC? Is the driver on the XP machine up to date?

If anyone could point me in the right direction I would be very grateful indeed! :banghead:

 

[mdlueck]

Let me tell back to you what I perceive your configuration to be, and you confirm the correctness...

1) Windows XP OS
2) Access 2000
3) Back end DB was SQL Server 2000
4) Back end DB needs to become SQL Server 2008 R2

Correct?

If so, what driver was the application using successfully to connect to SQL Server 2000?

For me, on Windows XP with Access 2007, the ODBC driver for SQL Server which ships with Windows XP has been working beautifully to connect to SQL Server 2008 R2. So that driver should be solid. I do not see how upgrading to SQL Server 2008 R2 would suddenly break that particular driver from working.

Have you tested the connection in Control Panel \ ODBC Settings? (Totally outside / separate from Access?)

 

[Grunners]

Hi Michael,

Thanks for getting back to me. Your 4 points are correct. I have also been testing the connection in the control panel - not been anywhere near Access up to this point.

What I have just done is install client tools from the SQL2008 DVD - and I got the SQL Native Client as an addition to my list of drivers. Unluckily it's given me exactly the same error message. You must be right about the ODBC driver.

I need to stew for a few minutes...

 

[mdlueck]

I got the SQL Native Client as an addition to my list of drivers. Unluckily it's given me exactly the same error message.

Sounds like you have an issue between the Windows client and new server, since both drivers give error messages connecting to the new server.

 

[Grunners]

I believe you may be right. Obviously the 'Dummies' book was way too advanced.

Thank you very much for your assistance. I can at least attack from a different angle now!

 

[pbaldy]

Shot in the dark here. I'm just on a iPad so can't look, but maybe Michael will know where it is. In SQL Server's settings is one that determines whether SQL Server security is used or just Windows authentication. Make sure both are enabled.

 

[Grunners]

Will do.

Found this in the logs, every time I try to create an ODBC conn' from the client machine:

05/24/2013 16:12:19,Logon,Unknown,Login failed for user 'Domain\craig'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.1.92]

This seems to happen a lot to people. There are all sorts of 'answers', like disable UAC and add all users to the sysadmin group!

I'll try your suggestion first...

 

[mdlueck]

This seems to happen a lot to people. There are all sorts of 'answers', like disable UAC and add all users to the sysadmin group!

PEOPLE!!!! :banghead:

Nothing like putt'n the K in Quality!