Thursday, March 29, 2012

DSN Connection to Remote SQL Server

Hi,

I've worked with SQL Server plenty but never in this specific manner. Hoping someone can shed some light.

I have a webform on web server A that needs to talk to a SQL server database that web server B accesses via its web application. I do not have
any privelidged access to server B or the sql server it uses (no FTP to webserver, no remote access via Enterprise Manager to the SQL server). The Admin of Server B told me that I can use a DSN connection to talk to the SQL server database that the web app on server B uses. He emailed me the names of the SQL database tables AND a copy of the web.config from server B which shows the DSN implemented like this:

<appSettings>
<add key="dsn" value="data source=sql3.domainname.net;database=dbname;User Id=dbuserid;password=dbpw;" />
<add key="default_language" value="en" />
<add key="error_email_to" value="someone@.domainname.com" />
<add key="baseHref" value="http://www.mysite.com/" />
<add key="website_name" value="$$$$$$$$$$$$$$$$$" />
</appSettings>

I was planning to run my webforms on Server A where I do have FTP, Control Panel, etc.
So, I wrote this code into my VB.NET codebehind file.

Dim oODBCConnectionAs OdbcConnection
Dim sConnStringAsString = _
"Driver={SQL Server};" & _
"Server=sql3.domainname.net;" & _
"Database=dbname;" & _
"Uid=dbuserid;" & _
"Pwd=dbpw"

oODBCConnection =New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()
... commands to access data and load something into a control on a webform
oODBCConnection.Close()

When I attempt to run the webform in my browser from Server A. I'm getting this error:
ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
The error is EXACTLY the same whether I place the appSettings section in server A's web.config or leave them out.

I have obvious questions about the error like...
Might sql3.domainname.net simply be refusing communication requests that don't come from the Server B domain?
Might the sql3.domainname.net be setup so that I need to use another protocol (not tcp/ip)?
Might my code need to be changed to support this type of connection (i.e. I didn't code it right)?

I spent some time readinghttp://support.microsoft.com/kb/328306/en-us but I'm at a loss as to what the best course is to take for debugging this.
Thanks in advance for any insight you can offer.

Stewart

Hi,

I think you should get the remote server's ip address and user account to creat a local DSN and call the DSN in your application. Another easy solution is to use SqlConnection rather then ODBCConnection and modify your connection string and make it look like "DataSource=IP address;Initial Catelog=DBName;User Id=;Password="

Thanks.

No comments:

Post a Comment