none
Sql Server Connection String

    Question

  • I am working on an application using Visual Basic 208 and SQL Server 2005 Express.

    The tables and queries were created using the Management Studio Express for Visual Studio Express 2005. All of the files are in the default location, which is:

    C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\EntertainmentCollectionData.mdf

    The connection string that I am using is:

    "server=localhost\SQLEXPR5;database=EntertainmentCollectionData.mdf; user id = sa;password=;"

    This results in an error message in Visual Basic "SQLException Was Unhandled" follwed by a lengthy message, the last part of which stated "error 26 -  Error locating Server/Instance Specified"

    Obviously my Connection String is inaccurate but I don't know how to change it.

     

    Thanks for any suggestions that you can give.
    sirmilt
    Wednesday, August 31, 2011 2:52 PM

Answers

  • You're mixing things up. In the default mode a database is always addressed by its name. Thus you connection string must use the database logical name, not its file name nor path, e.g.

    server=localhost\SQLEXPR5;database=EntertainmentCollectionData; user id = sa;password=;

    Take a look at www.connectionstrings.com for your possibilities. Also take a look at the user instance configuration.

    Wednesday, August 31, 2011 3:16 PM
  • Hello,

    I have always prefered (local) or . instead of localhost.

    http://blogs.msdn.com/b/sql_protocols/archive/2008/09/19/understanding-data-source-local-in-sql-server-connection-strings.aspx

    For your last problem, i would suggest you to have a look at this link :

    http://blogs.msdn.com/b/sql_protocols/archive/2008/05/03/understanding-the-error-message-login-failed-for-user-the-user-is-not-associated-with-a-trusted-sql-server-connection.aspx

    It's coming from the SQL Protocols blog : THE reference for any connection error with SQL Server

    I may have not understood your last connection string. I have seen 2 potential problems :

    - the SQL Server login sa is without password ( maybe you rub off for security ) , if it is not voluntary, it is an enormeous security hole. The security policy has changed with SQL Server 2008 R2 ( i think ), but it is impossible to create a SQL Server login with an empty password since 2008 R2, especially with sa which is the "super" sysadmin for SQl Server ( the most powerful login versus the SQL Server instance )

    - in your connection string, it is lacking integrated security = false to indicate you are using the SQL Server authentification. I know that it is not necessary but it is clearer, even if the provider is supposed to consider that it's a SQL Server authentification as soon as the 2 keywords User Id and Password are present

    Last point : there is a very nice , useful class in SqlClient namespace : SqlConnectionStringBuilder

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx

    if you use the properties of this class to set the values of your connection string, you will not have to remember the exact syntax for each field and , more clever and important, you will be able to use the intellisense in your VS.In the examples, use builder.IntegratedSecurity instead builder["Integrated Security"] or builder.InitialCatalog instead builder["Database"].Try to use the ApplicationName property to identify the application ( when you will search your connection in SSMS, it would be easier).

    You can get the connection string with the method ToString() of this class and you will be able to use this connection string in the constructor of SqlConnection

    Don't hesitate to post again for more help or explanations

    Have a nice day

    PS : Please, could you reread the 1st post of Stefan : it is clear, and it covers most of the possible cases


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Tuesday, September 06, 2011 12:53 PM
    Moderator

All replies

  • You're mixing things up. In the default mode a database is always addressed by its name. Thus you connection string must use the database logical name, not its file name nor path, e.g.

    server=localhost\SQLEXPR5;database=EntertainmentCollectionData; user id = sa;password=;

    Take a look at www.connectionstrings.com for your possibilities. Also take a look at the user instance configuration.

    Wednesday, August 31, 2011 3:16 PM
  • Thank you Stefan for the prompt reply.

    I tried the Line you quoted in your response and the ones shown in the Connection Strings web site, with no success. 

    Could the answer be in the security settings?

    Any other suggestions?


    sirmilt
    Wednesday, August 31, 2011 7:21 PM
  • What error message did you get?

    A typical connection strings look like

    <connectionStrings>
      <add name="connectionStringName1"
           connectionString="Database=YourDatabaseName;Server=(local)\SQLEXPRESS;Integrated Security=SSPI;"
           providerName="System.Data.SqlClient" />
      <add name="connectionStringName2"
           connectionString="Database=YourDatabaseName;Server=(local)\SQLEXPRESS;User ID=yourUserName;Password=yourPassword;"
           providerName="System.Data.SqlClient" />
    </connectionStrings>
    Thursday, September 01, 2011 7:54 AM
  • Thanks Stefan

    A copy of the error message is shown


    sirmilt
    Thursday, September 01, 2011 1:43 PM
  • This sounds like a normal connectivity problem. Read
    Steps to troubleshoot SQL connectivity issues.

    Thursday, September 01, 2011 2:13 PM
  • Stefan

    Sorry that the last post to respond to your earlier message was only the error message.  Here's where it currently stands.

    First, I wanted to tell you how much I appreciate your effors to help.

    I tried all the suggested connection strings  and the ones shown in "http://connectionstrings.com". Here are the last four that I used, all of which produced the same error:

           server=localhost\SQLEXPR5\MSSQL.2\SQLEXPRESS;database=EntertainmentCollectionData; user id = sa;password=;
           server=localhost\SQLEXPR5;database=EntertainmentCollectionData; user id = sa;password=;
           server=localhost\SQLEXPR5\SQLEXPRESS;database=EntertainmentCollectionData; user id = sa;password=;
           server=localhost\SQLEXPR5\SQLEXPRESS;database=EntertainmentCollectionData; user id = sa;password=;

    I'm at a loss as to how to proceed. I don't know if past changes are haning any effect, but here'a little "history".

    The database was first created using SQL Server 2008R2 Express and it's related management tool. This worked great, until I tried to build a deployment package using ClickOnce in Visual Basic. This didn't work because Microsoft did not include the package prerequisite for version 2008R2. I then uninstalled the SQL Server 2008R2 and its management tool, and installed the 2005 version which is supported, I rebuilt my database (6 tables and 30 queries).

    I'm at a loss.

     


    sirmilt
    Thursday, September 01, 2011 2:18 PM
  • Is the DB server and IIS server on the same machine?

    Have you tried to make an ODBC connection on your web server to it?  That would at least let you see if you can access the machine.

    If the services are on different machines is there any firewalls interfering ?

     

     


    IrishAdo irishado@hotmail.com
    Thursday, September 01, 2011 2:36 PM
  • So it works on your development machine, where you have installed a local SQL Server Express?

    How did you deploy the database?
    Is the production SQL Server on the same machine as the IIS? When not are they in the same domain (AD trusted)?

    Try testing the connection:

    1. Create a text file on the desktop.
    2. Rename the extension of that file to .udl, e.g. New Document.udl (ensure that you have unchecked Hide extensions in the Explorer settings before).
    3. Open this file (double-click it).
    4. Select on the first page (Providers) the SQL Server Native Client at the end of the list.
    5. Enter on the second page the SQL Server name and instance (when there is own used).
    6. Select the authentication mode and enter the credentials if needed.
    7. Now you should be able to select a database in the inital catalog combo-box.

    Thursday, September 01, 2011 2:46 PM
  • Thanks IrishAdo for the response.

    Yes, all on one machine. I'm wondering if my problem may be in the security settings and am checking this out.


    sirmilt
    Thursday, September 01, 2011 2:49 PM
  • Stefan

    You lost me at item 4 of your last message.

    Your comment at the beginning "So it works on your development machine.." should read  that it worked when I had SQL Server2008R2 on my machine, but it doesn't work with a new database in SQL Server2005. I had to change because I couldn't deploy an SQL Server 2008R2 database with Visual Basic soo8 application. This is what started thewhole problem.

    Milt

     


    sirmilt
    Thursday, September 01, 2011 3:23 PM
  • So how did you deploy the database to your new, local SQL Server Express 20065 installation. What did you do exactly?

    Thursday, September 01, 2011 3:29 PM
  • Step 4 is in the ODBC Connection manager

     

    Also try using localhost as the server name


    IrishAdo irishado@hotmail.com
    Thursday, September 01, 2011 3:31 PM
  • Hi,

     

    try to connect using this instance localhost\SQLEXPR5 at  Management Studio if you can connect.

     



    Regard, Ryan Lambatan
    Please "Mark as Answer" or "Vote as Helpful"
    Saturday, September 03, 2011 5:49 PM
  • This is my connection string:

    "server=localhost\SQLEXPR5;database=EntertainmentCollectionData; user id = sa;password=;"

    I'm not sure that I know what you mean by Connect at t Management Studio,

    the code executes to Coonection .Open and then throws the error:

    "Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection."

     

    Milt


    sirmilt
    Sunday, September 04, 2011 2:01 AM
  • Hello,

    I have always prefered (local) or . instead of localhost.

    http://blogs.msdn.com/b/sql_protocols/archive/2008/09/19/understanding-data-source-local-in-sql-server-connection-strings.aspx

    For your last problem, i would suggest you to have a look at this link :

    http://blogs.msdn.com/b/sql_protocols/archive/2008/05/03/understanding-the-error-message-login-failed-for-user-the-user-is-not-associated-with-a-trusted-sql-server-connection.aspx

    It's coming from the SQL Protocols blog : THE reference for any connection error with SQL Server

    I may have not understood your last connection string. I have seen 2 potential problems :

    - the SQL Server login sa is without password ( maybe you rub off for security ) , if it is not voluntary, it is an enormeous security hole. The security policy has changed with SQL Server 2008 R2 ( i think ), but it is impossible to create a SQL Server login with an empty password since 2008 R2, especially with sa which is the "super" sysadmin for SQl Server ( the most powerful login versus the SQL Server instance )

    - in your connection string, it is lacking integrated security = false to indicate you are using the SQL Server authentification. I know that it is not necessary but it is clearer, even if the provider is supposed to consider that it's a SQL Server authentification as soon as the 2 keywords User Id and Password are present

    Last point : there is a very nice , useful class in SqlClient namespace : SqlConnectionStringBuilder

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx

    if you use the properties of this class to set the values of your connection string, you will not have to remember the exact syntax for each field and , more clever and important, you will be able to use the intellisense in your VS.In the examples, use builder.IntegratedSecurity instead builder["Integrated Security"] or builder.InitialCatalog instead builder["Database"].Try to use the ApplicationName property to identify the application ( when you will search your connection in SSMS, it would be easier).

    You can get the connection string with the method ToString() of this class and you will be able to use this connection string in the constructor of SqlConnection

    Don't hesitate to post again for more help or explanations

    Have a nice day

    PS : Please, could you reread the 1st post of Stefan : it is clear, and it covers most of the possible cases


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Tuesday, September 06, 2011 12:53 PM
    Moderator
  • Thank you all for your help. I'm working to finish the project now and hope that there are no further problemss.

    Milt

     


    sirmilt
    Saturday, September 10, 2011 3:17 PM