none
Creating a connection String to Remote Sql Server

    Question

  • Hi,

    I am having an issue creating a connection string to a remote server. I have Sql Server 2008 installed, Remote connections are enabled, TCP/IP is also enabled. I have tested connecting via Sql Server Management Studio to the remote server and it connects fine, so I am sure that it is allowing connections.

    In my app.config file of my Windows Form Application I have the following connection String:

      <connectionStrings>
        <add name="WindowsFormsApplication1.Properties.Settings.AccountingConnectionString"
          connectionString=";Network Library=DBMSSOCN;Data Source=tcp:**.***.*.**,1433;Initial Catalog=Account;User ID =***;Password=***********;Encrypt=yes;Trusted_Connection=False"
          providerName="System.Data.SqlClient" />
      </connectionStrings>
    

    If I go to add a datasource to a Drop Down List, I click Add Project Source --> Select Database--> Click Next --> Select Dataset --> Click Next --> Select my connectionString(Settings) and click next. When I do I get the following Error:

    Failed to open a connection to the database

    "A network-related or instance-specific error occurred while establishing a connection to SQL Server. THe server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider:TCP Provider, error:0 - The requested name is valid, but not data of the requested type was found.)"

    Check the connection and try again.

    the only thing I noticed was that I did not have the DBMSSOCN.DLL file in my windows/system32 folder. I downloaded it and put it in there but it didnt help.

    Does anyone have any idea if it is because something might be wrong with my connection string or something else?

    Thanks,

    Chris

    Tuesday, July 20, 2010 3:01 AM

All replies

  • In above connection string what is "tcp" in data source ? Source=tcp:**.***.*.**,1433 ??

    It should be something like this.

    SQLConn.ConnectionString = "Network Library=DBMSSOCN;" & _
                                "Data Source=xxx.xxx.xxx.xxx,1433;" & _
                                "Initial Catalog=mySQLServerDBName;" & _
                                "User ID=myUsername;" & _
                                "Password=myPassword"

    Where:
    - "Network Library=DBMSSOCN" tells SqlConnection to use TCP/IP
    - xxx.xxx.xxx.xxx is an IP address. 
    - 1433 is the default port number for SQL Server
    - You can also add "Encrypt=yes" for encryption
     

    Tuesday, July 20, 2010 6:03 AM
  • the tcp part is from a suggestion I saw on a website on how to get itto use TCP so I gave it a try. I have tried the connection string from the website you got that from, but it still does not work, I get the same error in my original post. Is there a way to perhaps see which part of the connection string is causing the error?

    • Edited by Wozer Wednesday, July 21, 2010 2:34 AM
    Tuesday, July 20, 2010 10:18 PM
  • Doing somre more testing with creating a new connection string through the wizard. If you click on Advanced and under Network Library the TCP/IP library says DBMSGNET I tried using this to create a connection but still got the same error as before.

     

    any help would be greatly appreciated.

    Tuesday, July 20, 2010 11:58 PM
  • By looking at error message, seems you haven't enabled remote connection for SQL Server. This is needed when you access connection over network . There is a chance for error if Firewall is enabled for your machine. It will restrict inbound connections

    Can you please try these steps

    http://msdn.microsoft.com/en-us/library/dd857537(VS.85).aspx

    Wednesday, July 21, 2010 4:56 AM
  • Hi Mike,

    I had followed most of those steps before, I had not set up a user login for the database that I was wanting to use. I tried setting up a new user name and going that way but it still gave me the same error, I had previously been trying to use the sa login.

    I know the remote connection is enabled for SQL Server, I had connected remotely to the server from a different computer not on the same network using the ip address and the sa login and I was able to connect get in, and could create SP's add rows etc. It seems to be something in the connection string that is causing the error.

    Is there anyway way I can test different parts of the connection?

    Thanks again for your help so far

    Chris

    Wednesday, July 21, 2010 10:59 PM
  • Try using the format that specifies the server property instead. This is how I format all my connections strings:

    "server=ServerMachine;database=MyDatabase;uid=MyID;pwd=MyPassword"


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Thursday, July 22, 2010 4:58 AM
  • I gave that a shot bonnie, it didn't fix the error, but I now have a different one. Provider: SQL Network Interfaces, error: 28 - Server doesn't support the requested protocol. I have tried using DBMSSOCN and DBMSGNET and get that same error either way
    Thursday, July 22, 2010 11:32 AM
  • Hi Wozer,

    Could you connect to the SqlServer instance via other tools, such as SQL Server Management Studio?

    Regards,
    Aland Li


    Please mark the replies as answers if they help and unmark if they don't. This can be beneficial to other community members reading the thread.
    Monday, July 26, 2010 8:10 AM
    Moderator
  • Sorry for the delayed response Aland.

     

    I was able to connect to the SqlServer Instance with the Sql Server Mangement Studio Tool from another location not on my network.

     

    Chris

    Tuesday, August 03, 2010 2:04 AM
  • Hi Wozer,

    You need to run the Sql Server Management Studio on your machine. You could also move your application to the machine that the Sql Server Mangement Studio can connect successfully to see if it works.

    Regards,
    Aland Li


    Please mark the replies as answers if they help and unmark if they don't. This can be beneficial to other community members reading the thread.
    Tuesday, August 03, 2010 11:18 AM
    Moderator
  • Aland,

    I am able to connect to the SqlServer Instance from my computer as well, I just also tested from a remote computer to see if the SqlServer was working to accept remote connections correctly.

     

    Chris

    Tuesday, August 03, 2010 11:28 AM
  • Chris,

    Let's try it from a different angle ... could you post the code are you using to try to connect to SQL using that ConnectionString?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, August 04, 2010 1:23 AM
  • Bonnie,

    I am not using a connection string. I am doing the following:

     

    In my app.config file of my Windows Form Application I have the following connection String:

     <connectionStrings>
      <add name="WindowsFormsApplication1.Properties.Settings.AccountingConnectionString"
       connectionString=";Network Library=DBMSSOCN;Data Source=tcp:**.***.*.**,1433;Initial Catalog=Account;User ID =***;Password=***********;Encrypt=yes;Trusted_Connection=False"
       providerName="System.Data.SqlClient" />
     </connectionStrings>
    

    If I go to add a datasource to a Drop Down List, I click Add Project Source --> Select Database--> Click Next --> Select Dataset --> Click Next --> Select my connectionString(Settings) and click next. 

     

    That is where I get the error.

     

    Chris

    Thursday, August 05, 2010 11:31 PM
  • I'm sorry Chris ... you *did* say that in your very first post. My bad. I wish .NET wouldn't make it possible to add database stuff that way, because it's actually not a good practice to put data access stuff directly in your form. But, I won't bother ranting and preaching about that just yet.

    First, let's just see if you can get a connection to your database programmatically. Try this:

    // first get the connection string from your app.config
    ConnectionStringSettings ConnectionSettings = ConfigurationManager.ConnectionStrings["WindowsFormsApplication1.Properties.Settings.AccountingConnectionString"];
    string ConnectionString = ConnectionSettings.ConnectionString;
    
    // create a DataSet, just to have somewhere to show that we got some data
    DataSet ds = new DataSet();
    
    // Connect to the database, and Fill the DataSet
    SqlConnection conn = new SqlConnection(ConnectionString);
    SqlCommand sc = new SqlCommand("SELECT * FROM MyTable", conn);
    SqlDataAdapter da = new SqlDataAdapter(sc);
    
    // this will create and fill the the first DataTable in the DataSet 
    da.Fill(ds);
    
    // verify this by check to see if .Tables[0] exists
    // and that ds.Tables[0].Rows.Count > 0
    

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Friday, August 06, 2010 5:04 PM
  • Hey Bonnie,

     

    I gave the above code a shot (did a messagebox output if Rows.Count > 0 and got this error message:

     

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

     

    which showed up at the following code:

    da.Fill(ds);

    one question, in my connection string I have Server=**.***.**.**\MSSQLSERVER (so IP address and The Sql Server instance as it shows up in my SqlServer Config Manager. Is this the correct way to do this?
    Chris

    Saturday, August 07, 2010 11:54 PM
  • If I may suggest a way to get the proper connection string and then copy and paste it back to VS:

     

    1. create a new text document on your desktop - conn.txt 
    2. change file extension to udl (conn.udl)
    3. double click to open the file
    4. in the first tab select appropriate provider (I use "OLE DB Provider for SQL Server")
    5. in the second tab enter server name (**.***.**.**\MSSQLSERVER), username, password (check Allow saving password) and database name.
    6. test connection
    7. if the test reports success close the window.
    8. open the file with notepad, copy everything but the provider name and paste it back to connectionString attribute
    9. test it using Bonnie's code...

    more detailed tutorial here 

     

    Good luck :)

    best regards,
    Vladimir
    • Proposed as answer by Rashmi543 Friday, March 30, 2012 8:55 AM
    Sunday, August 08, 2010 12:20 AM
  • Vladimir --- good idea!

    Chris --- I think that you don't need to use the server instance, just the IP address (the only time you need the server instance is if you have more than one SQL Server, and thus one would be a named instance. Sorry I didn't think of that earlier, but that's probably where your problem lies.

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, August 08, 2010 1:06 AM
  • Vladimir,

     

    I gave your suggestion a shot and when I try to choose a database on the server I get the following error show up.

     

    [DBNETLIB][ConnectionOpen (Connect()).]Sql Server does not exist or access denied.

     

    I tried using just the ip, the ip,1443 for the port and ip/MSSQLSERVER all cases had the same error.

     

    Bonnie, I do actually have 2 instances, I have SQLServer Express installed as well (probably because I forgot to check it off when I installed Visual Studios) So I am assuming I do need the MSSQLSERVER in there?

     

    Chris

    Sunday, August 08, 2010 2:35 AM
  • Only one of them will be a named instance. I assume that SQL Express was installed second, so your initial SqlServer probably is NOT a named instance. Try taking it out of the equation (just use the ip) and see what happens!


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, August 08, 2010 5:26 AM
  • Hey Bonnie,

    I tried connecting through SQL Server management studio from another computer and it didnt work this time. I am going to try to figure out what changed and then see what happened. I will let you know when I get it worked out and the results.

     

    Chris

    Tuesday, August 10, 2010 11:23 AM
  • Hey Bonnie,

     

    I tried remotely connecting to my SQL Server, and took a look at my Event Viewer and got the following error:

     

    Login failed for user 'xxx'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: xxx.xx.xx.xx]

    Also It keeps saying the Source is MSSQL$SQLEXPRESS. This shows up regardless of if I put xxx.xx.xx.xx\MSSQLSERVER in my connection path or not.

    I have not changed anything from when I could last log in. Is there any way I can just remove the SQLExpress instance so it isn't trying to connect to that?

    Chris

    Wednesday, August 11, 2010 1:29 AM
  • Hmmm ... MSSQLSERVER is the default, unnamed instance, so you shouldn't ahve to include it in your connection string... As the matter of fact, when I include it in my udl file to test the connection, the test fails, when I remove it the test succeeds.  

    so first verify if you have a named instance or not, and whether Sql server authentication mode is enabled.

    Open Management studio on server, connect to the server, open properties, under General tab check the name, under Security tab check authentication mode.
    Under Connections make sure remote connections are allowed.

    are you using built in sa login, or some other ?

    have you by any chance did a backup/restore of the database in question from another server (restored backup created on another server) ? 

    Wednesday, August 11, 2010 2:26 AM
  • Vladimir,

     

    I checked what you said. Under the general Tab the name listed is the name of my Computer. Sql Server Authentication is enabled and remote connections are allowed. I have tried connecting using the built in sa as well as a user account I created, both are failing.

    I tried a couple ways of putting in the server name

    xx.xxx.xx.xxx which gives me the error of:

    Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied.

    if I do 

    xx.xxx.xx.xxx\MSSQLSERVER then I get

    Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen(ParseConnectParams()).]Invalid connection.

     

    Chris

    Wednesday, August 11, 2010 10:31 PM
  • This is frustrating, without the possibility to actually try something on site ...

    did you try to turn off firewall all together on the server machine and eliminate it as the possible cause ...

    can you ping server at all from your machine ?

    Saturday, August 14, 2010 12:14 PM
  • Vladimir,

     

    This is going to sound really stupid, I was messing around with my firewall to disable it to test, and I ran into the private IP address of the computer, I put that in as the Server Name and the Connection Works fine.

    Do I have to do something special to connect to the external IP of a computer that has a private IP?

     

    Chris

    Saturday, August 14, 2010 2:04 PM
  • This is how I did, and it worked:

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
     <configSections>
     </configSections>
     <connectionStrings>
     <add name="sqlConn"
      connectionString="Data Source=XXX.XXX.XXX.XXX,1433;Network Library=DBMSSOCN;Initial Catalog=DataBaseName;User ID=DBUserName;Password=DBPassword"
      providerName="System.Data.SqlClient" />
     </connectionStrings>
    </configuration>
    

    Issues to consider:

    1. XXX.XXX.XXX.XXX is the IP of the pc on which is the Remote server

    2. DataBaseName is your databsase name (but only a name, without the endings - if the full dbName is "YourServerName.YourDBName.dbo", you only write "DBName"

    3. DBUserName is your username, with which you access to this database (when logging into it)

    4. DBPassword : is your passwork to access to the database!

     

    Thats all.

    Mitja

     

    Saturday, August 14, 2010 2:32 PM
  • Vladimir,

     

    This is going to sound really stupid, I was messing around with my firewall to disable it to test, and I ran into the private IP address of the computer, I put that in as the Server Name and the Connection Works fine.

    Do I have to do something special to connect to the external IP of a computer that has a private IP?

     

    Chris

    OK, your private address is something like

    192.168.x.x  or
    172.160.x.x  or
    10.0.x.x

    And by public address you mean the address of the router ?

    I'm not an networking expert, but why would you want to access SQl server using the public address ?
    Usually application server is on the same machine or same LAN as the SQL server.

    If you don't have a client-server architecture and need to connect to DB from a computer that's not in LAN (e.g. notebook from home) consider connecting to VPN first and then connect to DB from your app using private address.

    Anyway, if you still want to connect using public address you might need to forward port 1433 to the machine hosting Sql server.

    Again, I'm not a network expert or anywhere close to it ... just telling it from my humble experience in that field.

     

    Best regards,
    Vladimir

    Saturday, August 14, 2010 5:05 PM