• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

Remote SQL Connection string for Visual Studio

I'm making the switch from DreamWeaver to VStudio 2005.  It's killing me ...

All i want to do is develop on a local machine to a SQL 2005 server on a remote host.  Here is the connection string i'm attempting to use ... no good.

Provider=SQLOLEDB;Data Source=<Remote SQL Server IP ADDRESS>,1433;UID=SA;Password=pww;Initial Catalog=DBSQL;Network Library=DBMSSOCN;

The connection string inside DreamWeaver - THAT WORKS is:

               Provider=SQLOLEDB.1;Data Source=U15169771\SQLSERVER;UID=SA;Password=pww;Initial Catalog=DBSQL;

you'll notice that the name of the SQL server on the reomte server is U15169771\SQLSERVER.  So ... I've tried to include that in the new "Data Source'" tag using UNC path like ...

               Data Source=<Remote SQL Server IP ADDRESS>,1433/U15169771\SQLSERVER - doesn't work ... obviously.

What am i doing wrong or what do i need to do to make a connection to the remote SQL server.  

I need a connection string that works.

Thanks,

Gp.
0
timbersnow
Asked:
timbersnow
  • 8
  • 6
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
U15169771 appears to be the server name. Therefore your connection string should be something like (Assuming SQLSERVER is the correct name for the instance):

    "Data Source=<IP Address>\SQLSERVER;UID=sa;PWD=pww;Initial Catalog=DBSQL;"

This of course also assumes that the SQL Server is available to the outside world.
0
 
timbersnowAuthor Commented:
you're right ... U15169771 is the server name and SQLSERVER is the correct name for the instance ...


Here is the exact connection string

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Provider=SQLOLEDB;Data Source=<IPADDRESS>\SQLSERVER;UID=SA;PWD=pss;Initial Catalog=DBSQL;"
            ProviderName="System.Data.OleDb" SelectCommand="Select"></asp:SqlDataSource>

when i try to load the schema - it prompts me for a userid and password - and i KNOW the userid and password are correct.  Keep in mind that this connection string is working inside of DreamWeaver (less the IP address - using the site definition in Dreamweaver).

What gives?

I HATE feeling STUPID!

Gp.
   
0
 
Carl TawnSystems and Integration DeveloperCommented:
What do you mean by "when i try to load the schema" ? And what is it that is prompting you for a password ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
timbersnowAuthor Commented:
When i hit the TEST button

Visual Studio prompts me after it tries to connect - i enter it - it comes back that it cannot connect.

0
 
Carl TawnSystems and Integration DeveloperCommented:
Does it give a reason why it cannot connect ?
0
 
timbersnowAuthor Commented:
Yep ...

Unable to retrieve schema.  Ensure that the ConnectionString and SelectCommand properties are valid.  An error has occured while establishing a connection to the server.  When connecting to SQL Server 2005, this feature may be caused by the fact that under the default settings SQL Server does not allow remote connecitons. (provider: SQL Netowrk Interfaces, error: 26 - Error Locating Server/Instance Specified)

HOWEVER ... it works - same connection string - under Dreamweaver.

Argg


0
 
timbersnowAuthor Commented:
AND ... TCP/IP is enabled under the SQLSERVER Network Configuration ... so the whole error 26 deal is not because of that ...
0
 
Carl TawnSystems and Integration DeveloperCommented:
Try changing your DataSource to:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=<IPADDRESS>\SQLSERVER;UID=SA;PWD=pss;Initial Catalog=DBSQL;" ProviderName="System.Data.SqlClient" SelectCommand="Select"></asp:SqlDataSource>
0
 
timbersnowAuthor Commented:
Argg ... nope

This is freaking pissing me off!!!! (not you)

Don't get it ...
0
 
Carl TawnSystems and Integration DeveloperCommented:
Maybe the issue is "Error Locating Server/Instance Specified". Try just using the name instead of the IP, as you do with DW.
0
 
timbersnowAuthor Commented:
i've used the IP w/DW as well ... worked.  And if i don't use the ip - how will it find the server/instance?

This has to be something simple.
0
 
Carl TawnSystems and Integration DeveloperCommented:
Maybe this will give you some things to try:

    http://www.datamasker.com/SSE2005_NetworkCfg.htm
0
 
timbersnowAuthor Commented:
Figured it out ...

HERE:  http://support.microsoft.com/default.aspx?scid=kb;en-us;823938

I'll read your link - and i'm going to award you the points cause you stuck with me.  

Appreciate it.

Gp.
0
 
timbersnowAuthor Commented:
My freaking server was using a different port ... and i have NO idea how that got set, but it's working now ...

Here's what i ended up with.

Provider=SQLOLEDB;Data Source=<IPADDRESS>,5767\U15169771\SQLSERVER;Password=pwd;User ID=SA;Initial Catalog=DBSQL;Network Library=DBMSSOCN

Awarding you the points now.

CHeers - and THANKS.

Gp.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now