Friday, March 30, 2012

One format can connect to Sql Server but others cannot

I have setup a SQL Server 2000 database on a remote machine. In the SQL's Enterprise manager I can connect remotely to the database and perform operations. In Visual Studio 2005 I can create a new data connection only using ODBC to connect. Also I can ISQL to the remote machine with no problems.

But when I try to connect using the driver Microsoft SQL Server (SQL connect) as the underlying connection platform in VS Studio, it cannot create a data connection that way, nor can I programmatically connect to the database using the .Net data driver. They timeout....

Setups

Windows Authentication used.
On remote Windows 2003/IIS6 I have local admin privlidges (domain/username) .
My remote account (domain/username) is an admin in the database
Remote has local copy account, same name/ password for impersonation.
Web service (see error #2) impersonates as my client account. (Anonymous access (true) -> my domain account/password and Autenticated access: Integrated Windows authentication set to true)

Error Messages

Direct from code (console app calling .Net data provider) to assembly doing the DB work.

System.Data.SqlClient.SqlException : Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.


Direct From Web Service to assembly doing the DB work

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.


Visual studio 2005 timesout in trying to make connection using Microsoft SQL Server (SQL connect) but can connect via ODBC>

Please advise / advTHANKSance

have you confirmed that Sql Native Client is installed correctly on the server?

In a nutshell, SQL Native Client is a stand alone data access Application Programming Interface (API) that is used for both OLE DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC driver into one native dynamic link library (DLL) while also providing new functionality above and beyond that supplied by the Microsoft Data Access Components (MDAC). SQL Native Client can be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features such as Multiple Active Result Sets (MARS), User-Defined Types (UDT), and XML data type support.

Derek

|||Even though the error message hints at 2005, the sql server is a 2000 server. Does SNC still apply?|||

i was/am assuming this is in the context of a SQL Server Project? Meaning you can only target SQL Servers 2005+ and by default the connection manager uses SQLNCLI.

Derek

sql

No comments:

Post a Comment