none
Coonection string for SQL Server 2008 Enterprise Edition RRS feed

  • Question

  • I have been struggling to support a remote user who is trying to connect to a SQL Server Enterprise 2008 edition uising a Windows Forms applicaiton written in VS 2008 Express edition. We tried the following different connection strings but nothing worked. Can you make a SPECIFIC suggestion or provide a string that you think might work?

    Note

    pasword is blank.

    Server Name is LATITUDE-yyyy\mlinsql

    Thanks.

    1-Data Source= LATITUDE-yyyy\mlinsql;Initial Catalog=ZZZZ;User id=xxxx;Password=;

    2-Data Source=(local);Initial Catalog=ZZZZ;user id=xxxx;password=;

    3-Data Source=Server= LATITUDE-yyyy\mlinsql;Database=ZZZZ;Trusted_Connection=True;

    4-Data Source=(local);Initial Catalog=ZZZZ;Integrated Security=True providerName="System.Data.SqlClient"

    5-Data Source=localhost;Initial Catalog=ZZZZ;user id=xxxx;password=;

    Sunday, March 28, 2010 3:22 PM

Answers

  • Okay, let's step through the alternatives. It would also help to give us the exception message you get from each of these attempts.

    1) This approach assumes the server has mixed-mode security enabled. It's off by default. It assumes the Login (user id) referenced has rights to the initial catalog. It also assumes that the DNS server can resolve the name "LATITUDE-yyyy". I would ping the server name to see if it responds.

    2) If the server is indeed local (running on the same system as the client application trying to establish the connection), you can (should) use the Shared Memory Provider invoked by (local) or . as the server name. However, you still have to provide the instance name as in:

    Data Source=(local)\mlinsql;Initial Catalog=ZZZZ;user id=xxxx;password=;

    3) -Data Source=Server= LATITUDE-yyyy\mlinsql;Database=ZZZZ;Trusted_Connection=True;
    This is just wrong. While you can use either "Data Source=" or "Server=" you can't use both. Next, once that's fixed you need to ensure that the current authenticated Windows user has a login on the target host SQL Server or belongs to a group that has rights and rights to the initial catalog.

    4) Again, this is wrong. The other connection strings you provided were (appear to be) based on using ADO.NET and SqlClient to access the server. There is no "ProviderName" (see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx) key. Now if you're trying to connect via OLE DB or the OleDb provider in .NET then you have a different set of keys to use and none of the above will work.

    5) LocalHost is a value used with IIS, not SQL Server.

    See Chapter 9 of my 7th Edition for a detailed explaination of how to get connected. I also have written any number of whitepapers and blog postings on the same.

     

    hth


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Monday, March 29, 2010 6:17 PM
    Moderator

All replies

  • www.connectionstrings.com

    Besides, can that user connect through some other application, i.e. sql management studio? Just make sure the sql server/firewalls configuration works.

    Or perhaps even easier, user should start Visual Studio and use Server Explorer to connect to your server. If it succeeds then he'll find the connection string in properties window.


    Miha Markic [MVP C#] http://blog.rthand.com
    Monday, March 29, 2010 6:24 AM
  • The first connection string should work as long as application has an access to the network where SQL Server is running. What kind of error do you receive? What is the definition of "remote user" in your case? Is this user located inside of the domain network where SQL Server is installed?
    Val Mazur (MVP) http://www.xporttools.net
    Monday, March 29, 2010 9:47 AM
    Moderator
  • There are many reasons why remote connections to SQL Server may be blocked. Here are a few things to check for:

    1) Verify that the firewall on the Server hosting the SQL Server does not block ports used by SQL Server and SQL Server Browser. If you are using the DTC or have any distributed tranction, ensure that the MSDTC ports are also not blocked by the firewall. You can verify this from Control Panel -> Windows Firewall -> "Exceptions" Tab or "Allow a Program..." Link (based on the OS).

    2) Ensure the SQL Server browser service is running. You can verify this from "SQL Server Configuration Manager".

    3) Ensure that the SQL Server supports Mixed Mode Authentication. You can verify this Looking into the Server Properties in the SQL Server Management Studio.

    4) Verify from the client machine (which is running the Windows Forms Application), that you are able to connect to the Remote SQL Server with the User ID and Password. You can do this in many ways, one of the simplest ways is by creating a .udl file and testing the connection to the server/database with the user id and password.
    Monday, March 29, 2010 5:10 PM
    Moderator
  • Okay, let's step through the alternatives. It would also help to give us the exception message you get from each of these attempts.

    1) This approach assumes the server has mixed-mode security enabled. It's off by default. It assumes the Login (user id) referenced has rights to the initial catalog. It also assumes that the DNS server can resolve the name "LATITUDE-yyyy". I would ping the server name to see if it responds.

    2) If the server is indeed local (running on the same system as the client application trying to establish the connection), you can (should) use the Shared Memory Provider invoked by (local) or . as the server name. However, you still have to provide the instance name as in:

    Data Source=(local)\mlinsql;Initial Catalog=ZZZZ;user id=xxxx;password=;

    3) -Data Source=Server= LATITUDE-yyyy\mlinsql;Database=ZZZZ;Trusted_Connection=True;
    This is just wrong. While you can use either "Data Source=" or "Server=" you can't use both. Next, once that's fixed you need to ensure that the current authenticated Windows user has a login on the target host SQL Server or belongs to a group that has rights and rights to the initial catalog.

    4) Again, this is wrong. The other connection strings you provided were (appear to be) based on using ADO.NET and SqlClient to access the server. There is no "ProviderName" (see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx) key. Now if you're trying to connect via OLE DB or the OleDb provider in .NET then you have a different set of keys to use and none of the above will work.

    5) LocalHost is a value used with IIS, not SQL Server.

    See Chapter 9 of my 7th Edition for a detailed explaination of how to get connected. I also have written any number of whitepapers and blog postings on the same.

     

    hth


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Monday, March 29, 2010 6:17 PM
    Moderator
  • Dear Gents,

    Thank you all for your help. The user was attempting to connect to a local server and after taking all your comments in consideration, this string has worked:

    Server=LATITUDE-yyyy\mlinsql;Database=ZZZ;Trusted_Connection=True;

    I wish one of the capable gurus in MS or elsewhere, coul code a tool that would help with the deployment process with regards to connection strings. For example the tool would list SQL servers on the machine and ask the user which provider you want to user, etc. and generates the connection string. The UDL approach is good but complex for end-user specially in remote situation.

    Monday, March 29, 2010 8:20 PM
  • I totally agree. I've started work on just such a program but never found time to complete it. Perhaps you've incentivized me to finish it... ;)

    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, March 30, 2010 5:46 PM
    Moderator
  • Hi Wiliam,

    I am sure this will be a great tool. Thanks.

    EK

    Wednesday, March 31, 2010 12:22 AM