Upsized Access DB ODBC error when run from UNC....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Upsized Access DB ODBC error when run from UNC....

Author

Topic

arichings
Starting Member

6 Posts

Posted - 2009-10-27 : 10:20:09

Greeting folks

Been tearing my hair out over the last 2 weeks with this and I've finally given up and posting here for help!!!!!

I've got an upsized access 2000 database that's presented to clients via UNC share. For love nor money I can't get the clients talking to the SQL backend data server. ODBC error every time from the client BUT NOT the server that hosts the UNC Share.

Where / how do I troubleshoot ODBC connection error? Where are ODBC logs witten? The event logs on all the server & clients are clean!

Tried the ODBC tracing option, disabling antivirus, various methods for linking ODBC's!!! Argghhhh

Background

SQL Server

Windows Server 2003 R2 SP2
SQL 2005 STD (SP3)
Upsized Database is in 2k format (80)

Access UNC Server

Windows Server 2003 R2 SP2
Trusted Link to SQL Server using an Windows Account with DBO rights into upsized database
Access front end WORKS when launched from this server (Access 2003 installed)

Clients

Windows XP
MDAC 2.8 with latest SP
Office 2003 Installed
____________________
Yep, its the end....

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-27 : 11:10:12

It could be a double-hop issue.

Have you tried connecting with a SQL Server login?

arichings
Starting Member

6 Posts

Posted - 2009-10-27 : 11:16:54

Cheers for the reply.

Yep, tried connecting with SQL login details (SA, specific user account), not joy....

____________________
Yep, its the end....

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-27 : 11:23:14

What error message do you have in the SQL Errorlog at the time of failed connection?

arichings
Starting Member

6 Posts

Posted - 2009-10-27 : 11:34:59

quote:
Originally posted by YellowBug

SQL Errorlog

As in Server -> Management -> SQL Server Logs -> Current

Nothing seen in the logs for about 20min either side of the connection attempts. In fact the SQL logs are very clean not much going on. This server is new and this is the 1st database to be hosted on it....

I suspect the ODBC call isn't even making it as far as the sql server....

____________________
Yep, its the end....

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-27 : 11:42:33

Can you confirm that you have selected "failed logins" auditing?
You can also run SQL profiler to monitor connections.

What is the connection string?
You say it works from serverA to serverB - what is the connection string used here?
And what is the connection string from client to server?

arichings
Starting Member

6 Posts

Posted - 2009-10-27 : 11:57:00

Enabling Failed Auditing on the windows side? Yep that setup & running.

ServerA to SQLServer connection is done via a ODBC with the following config (I assume that "connection string" reference) see summary below..... xxxx = security info removed...

Microsoft SQL Native Client Version 09.00.4035

Data Source Name: International Clientele
Data Source Description:
Server: GBLLBG132a.xxxx.xxxx.xxxx\GBLLBGSQL01
Use Integrated Security: Yes
Database: International_Cxxxxxx
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes

I then use the Access Link Table manager to connect the Access Data within that database to the SQL Server DB using the ODBC above...

The clients then connect to a UNC share to read the frontend accessdatabase.mdb My understanding is that when the client wants access to "real" data, ServerA uses the ODBC link to read data from the SQLServer and then ServerA passes the information back to the client (i.e. all done via the ODBC on the server not locally controlled ODBC on clients)

Thanks for your help thus far, too! Much appreciated....

____________________
Yep, its the end....

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-27 : 12:19:11

quote:
Enabling Failed Auditing on the windows side? Yep that setup & running.

This is under the SQL Server Properties. Right-click the SQL Server in SSMS and select Properties and then Security.

You need to establish whether you are connecting to the SQL Server. Try
- Start profiler on the SQL Server (default trace)
- connect from serverA to SQL Server (test the ODBC connection).
- - Is this connection shown in profiler?
- - What user is connecting to SQL Server?
- - Any message in the SQL Errorlog?
- Open the DOS prompt and make a connection to the SQL Server, using SQLCMD

Post any results/errors.

arichings
Starting Member

6 Posts

Posted - 2009-11-02 : 06:21:04

OK only login failed connections are being audited within SQL.

Started the Profiler. I can see my successful connection when I access the upsized database when logged into ServerA.

Audit Login -- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Microsoft Office 2003 usi06apx WW00x\usi06apx 15796 53 2009-11-02 11:22:32.497
SQL:BatchStarting SELECT Config, nValue FROM MSysConf Microsoft Office 2003 usi06apx WW00x\usi06aps 15796 53 2009-11-02 11:22:32.497
SQL:BatchCompleted SELECT Config, nValue FROM MSysConf Microsoft Office 2003 usi06apx WW00x\usi06apx 0 2 0 0 15796 53 2009-11-02 11:22:32.497 2009-11-02 11:22:32.497
SQL:BatchStarting SELECT "dbo"."OrdItemShipRequest"."OrdItemShipRequest_ID" FROM "dbo"."OrdItemShipRequest" Microsoft Office 2003 usi06apx WW00x\usi06apx 15796 53 2009-11-02 11:22:32.497
SQL:BatchCompleted SELECT "dbo"."OrdItemShipRequest"."OrdItemShipRequest_ID" FROM "dbo"."OrdItemShipRequest" Microsoft Office 2003 usi06apx WW00x\usi06apx 0 40 0 0 15796 53 2009-11-02 11:22:32.497 2009-11-02 11:22:32.497

When I access the same upsized database from the UNC share on ServerA on a client PC, the profiler show nothing (no connection)....

= ODBC failure on ServerA ???

____________________
Yep, its the end....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources