none
SQL Server Connection String

    Question

  • Hi All,

    1. I'm a bit confused as to the connection string to use, or whether the SQL server is really running to receive the connection that I'm trying to initiate.

    I have SQL Express 2008 R2 in a remote server, I have verified that the services is running, and that the firewall is set to accept connection to port 1433, and that the TCP is enabled. I'm trying to connect to the said remote server from my computer. (I have full access to the remote server, via RDP).

    I have this connection string (from this site: http://www.connectionstrings.com/Articles/Show/all-sql-server-connection-string-keywords):


    "Provider=SQLNCLI10;Integrated Security=SSPI;Persist Security Info=FALSE;Initial Catalog=myDB;Data Source=myIP_Address,1433;User Id=myuser;Password=mypass;"

    I'm getting this error:

    Error connecting: Login failed. The login is from an untrusted domain and cannot be used with Windows Authentication.

    I'm confused because I have set mixed authentication mode in the server. I have set the user and password in the SQL and that is what I'm using to connect to the SQL server.

    What am I missing? 

    2. I have another question, assuming I have two SQL server express running, SQL Express 2008 SP1 and SQL EXpress 2008 R2. How do I connect to the server that I want to connect to. Can I do  server=myIP_ADDRESS\INSTANCE_NAME,PORT#?

    Thanks in advance everyone....

    Thursday, February 24, 2011 11:01 AM

Answers

  • By default, SQL-Server Express will install as a named instance under the name SQLEXPRESS.  For a named instance, you must specify either its name or the port to be used.  It unlikely (but not impossible) that the port to use for a named instance is 1433 so the connection string should be something like:

    "Provider=SQLNCLI10;Initial Catalog=myDB;Data Source=myIP_Address\SQLEXPRESS;UserId=myuser;Password=mypass;"

    Notice that I have also removed the parameter "Integrated Security=SSPI" because you don't want to use Windows Authentication.

    For this to work, the port 1434 must also be open for UDP traffic and the SQL-Server Browsing service must be up and running on the remote server.

    If you don't want to open the port 1434, the instance name cannot be resolved by the Browsing server and you will have to specify the port instead:

    "Provider=SQLNCLI10;Initial Catalog=myDB;Data Source=myIP_Address,1433;UserId=myuser;Password=mypass;"

    Again, it's unlikely that the port to use here is 1433 for a named instance. Check the configuration.

    For your second question, replace SQLEXPRESS in the above connection string with the name of the second instance or specify the port to be used.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    • Marked as answer by labyou Thursday, February 24, 2011 1:49 PM
    Thursday, February 24, 2011 11:19 AM

All replies

  • By default, SQL-Server Express will install as a named instance under the name SQLEXPRESS.  For a named instance, you must specify either its name or the port to be used.  It unlikely (but not impossible) that the port to use for a named instance is 1433 so the connection string should be something like:

    "Provider=SQLNCLI10;Initial Catalog=myDB;Data Source=myIP_Address\SQLEXPRESS;UserId=myuser;Password=mypass;"

    Notice that I have also removed the parameter "Integrated Security=SSPI" because you don't want to use Windows Authentication.

    For this to work, the port 1434 must also be open for UDP traffic and the SQL-Server Browsing service must be up and running on the remote server.

    If you don't want to open the port 1434, the instance name cannot be resolved by the Browsing server and you will have to specify the port instead:

    "Provider=SQLNCLI10;Initial Catalog=myDB;Data Source=myIP_Address,1433;UserId=myuser;Password=mypass;"

    Again, it's unlikely that the port to use here is 1433 for a named instance. Check the configuration.

    For your second question, replace SQLEXPRESS in the above connection string with the name of the second instance or specify the port to be used.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    • Marked as answer by labyou Thursday, February 24, 2011 1:49 PM
    Thursday, February 24, 2011 11:19 AM
  • Thank you Sylvain. I was able to connect without the Integrated Security=SSPI. I understand now that port 1434 should be open in order to use this key=value pair. And that it uses Windows authentication.

    I tried the above connection string in VB2010 and it is having issue when used in the SQLConnectionStringBuilder, the error "Keyword not supported: provider" occurs. I deleted the Provider key to make it work. Take note that the above connection string worked in VB6.

     


    Thursday, February 24, 2011 1:55 PM