Solved

'SQL server' driver over internet ODBC not working.

Posted on 2011-09-06
10
330 Views
Last Modified: 2012-06-27
Hiya,
I have a remote server with a SQL Server 2008 database on it.

I have two desktop machines.

One machine connects to the SQL Server database over the internet using the 'SQL Server' driver. The other machine refuses to connect using the same driver but will connect using the 'SQL Server native Client' driver. However there seems to be an error produced when using the 'Native Client' driver whn accessing stored procedures.

I have checked the driver version on both machines = All good - Exactly the same.

Checked that firewall is switched off on both machines.

I am using identical connection strings.

Any ideas?
0
Comment
Question by:splanton
  • 6
  • 4
10 Comments
 
LVL 12

Expert Comment

by:NormanMaina
ID: 36487918
you can give us the errors from both types of connections -when trying to open using the sql server driver and the error when accessing stored procedures when using native client.

My feeling is authentication error for the sql driver connection and an authorization error for the native client access to sps
0
 
LVL 2

Author Comment

by:splanton
ID: 36487970
The error when connecting using the SQL Server' driver is this:-

SQL State: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server driver][DBNETLIB]ConnectionOpen
(Connect).
Connection Failed:
SQLState:'08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server driver][DBNETLIB]SQl Server Does not Exist or Access denied.

Just tried it on another machine and it doesn't work from that either. So now I have two machines that can't use the 'SQL Server' driver over the internet and one that does.

I can also now confirm that I can connect on one of the 'bad' machines to the SQL Server instance over the internet using the SQL MS with no problems at all, but still no ODBC!. ARRRRGH!
0
 
LVL 2

Author Comment

by:splanton
ID: 36487973
I can't give you the 'Native' driver error atm. Not in a position to test it.
0
 
LVL 12

Expert Comment

by:NormanMaina
ID: 36488013
Ok,try these:
1.check your connection string(if its integrated authentication,made sure the user logged in has permissions on sql server)
2.Restart all the sql server services including sql browser
3.Check no other firewalls are interfering with the connection -like anti virus firewalls.

Here is Microsoft's description of the error:
http://support.microsoft.com/kb/328306
0
 
LVL 2

Author Comment

by:splanton
ID: 36488032
OK,

Connection string = check.
Server rebooted = check.

The daft thing is that I have two desktops sat here - one connects just fine the other doesn't. Which would lead me to think that it isn't a server side issue.

I wouldn't mind but these are two relativly fresh installs of windows 7. Something is different but I have no idea how to ascertain where the issue with the connectivity is. Are there any tests I can do to help me track down the culprit?
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 12

Expert Comment

by:NormanMaina
ID: 36488083
No firewalls -anti virus on the clients?

The fresh install of windows 7,you need to make sure that the client tools are correctly installed.
0
 
LVL 2

Author Comment

by:splanton
ID: 36488113
OK, can you be more specific about client tool please?
0
 
LVL 12

Expert Comment

by:NormanMaina
ID: 36488481
the Client connectivity components

http://www.microsoft.com/download/en/details.aspx?id=23734

The other thing you need to look at is creating  a DSN for the connection
http://broadwin.com/Manual/EngMan/23.3.3_Create_an_ODBC_DSN_to_SQL_Server.htm
0
 
LVL 2

Accepted Solution

by:
splanton earned 0 total points
ID: 36582459
The problem was with the ADO driver. Even though it was set to detect the SQL Server port (1433) it requires a definitive port to be specidied to actually work!
0
 
LVL 2

Author Closing Comment

by:splanton
ID: 36708120
Tiral and error and blatent pot luck led me to use a ,1433 at the end of the connection string and behold, it now connects. Sorry for any wasted time every article I read never mentioned this syntax or the need for a port when using the default 1433 for SQL Server.
0

Featured Post

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

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 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

19 Experts available now in Live!

Get 1:1 Help Now