none
How do i connect a C# application to a SQL Server located accross the network? RRS feed

  • Question

  • CASE

    Hi, ive designed an app using C# that uses a SQL Server 2005 Express database.

    I wish to deploy the app to 10 machines in our organization.  And place the database file on the companies network.

    I have set all firewall properties in accordance with this.

     

    PROBLEM

    If i install MS SQL Server management studio express on one of the ten pc's i am able to connect to the database

    using Management Studio.  All i do is supply the ip address user name and password. No problem there.

     

    HOWEVER, if i use my C# application to connect, it kicks an error Login failed for user \SQLEXPRESS\Gust.

    WHY IS THIS?  Can someone please correct my connection string for me.  id completely appreciate this.

     

    current string is

    Data Source=.\SQLEXPRESS;AttachDbFilename=C:\AMI\IStudio.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

     

    what am i doing wrong here???

     

    thanks

    Mohamed

     

     

    Thursday, March 6, 2008 9:34 AM

All replies

  •  

    Hi and Hello

     

    You can use this connection and change your current connection.

     

    Your Current connection:

    Data Source=.\SQLEXPRESS;AttachDbFilename=C:\AMI\IStudio.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

     

    You can use this:

    Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=C:\AMI\IStudio.mdf; Database=dbname;Trusted_Connection=Yes;
     
    You can visit this site for other information about the connection
     
     
    tnx and God bless
    Thursday, March 6, 2008 9:46 AM
  • try this

    To use the User Instance functionality you need to enable it on the SQL Server. This is done by executing the following command: sp_configure 'user instances enabled', '1'. To disable the functionality execute sp_configure 'user instances enabled', '0'.

     

    Thursday, March 6, 2008 9:49 AM
  • Hello,

     

    Have a look on

    http://msdn2.microsoft.com/en-us/library/ms143684.aspx

    and in it :

    The network protocol support for user instances is local named pipes only.

    User Instance does not support SQL Server Authentication. Only Windows Authentication is supported

    The user instance shares the registry entries of the parent instance.

    If the parent instance is not residing on the computer executing the application, i don't think you will be able to share the registry entries of the parent instance.

     

    I have a bad fealing about

     

    The network protocol support for user instances is local named pipes only.

    I 'm wandering whether the real meaning : no network connection

     

    For me, user instances can only be used on the computer where is residing the file .mdf so you will never be able to synchronize all the databases

     

    Could you post the exact error message you got ?

    put your SqlConnection ( connection in the example ) in a try/catch

    try

    {

    connection.Open();

    }

    catch ( SqlException ex )

    {

    String  _message;

    _message = "Error in connection " + ex.Message + Environment.NewLine;

    _message += "  Errors : " + ex.Errors.Count.ToString() + Environment.NewLine;

    foreach ( SqlError item in ex.Errors )

    {

    _message += item.Message + Environment.NewLine;

    }

    MessageBox.Show(_message);

    // you are able to get all the errors with a message nearly clear

    }

     

    It may be complicated but really efficient to understand what's happening

     

    Have a nice day

    Thursday, March 6, 2008 12:27 PM
  •  Mr. A, Mohamed imtiyaz wrote:

    CASE

    Hi, ive designed an app using C# that uses a SQL Server 2005 Express database.

    I wish to deploy the app to 10 machines in our organization.  And place the database file on the companies network.

    I have set all firewall properties in accordance with this.

     

    PROBLEM

    If i install MS SQL Server management studio express on one of the ten pc's i am able to connect to the database

    using Management Studio.  All i do is supply the ip address user name and password. No problem there.

     

    HOWEVER, if i use my C# application to connect, it kicks an error Login failed for user \SQLEXPRESS\Gust.

    WHY IS THIS?  Can someone please correct my connection string for me.  id completely appreciate this.

     

    current string is

    Data Source=.\SQLEXPRESS;AttachDbFilename=C:\AMI\IStudio.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

     

    what am i doing wrong here???

     

    thanks

    Mohamed

     

     

    You seem to be using the Windows Guest account, which might not be a good idea over the long haul.  To use the Guest account you need to have that account enabled on the server and on each of your 10 PCs.  It might be better to create a separate account for each user on the server.

     

    Keep in  mind that each user needs to connect to Windows, and then the Server.  That's 2 accounts you need to setup.  Once connected to the server, you need to setup security and login on which database they need to connect to.  Once they are connected, you need to grant them priviliges to do stuff with that database.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2164297&SiteID=1

     

    Take a look at that post.  It describes connecting using Windows authentication, and how the Guest account fooled me into thinking that I had it working.  Then it describes how to connect using SQL Authentication.

     

    Rudedog

    Thursday, March 6, 2008 3:08 PM
  •  Rudedog2 wrote:
     Mr. A, Mohamed imtiyaz wrote:

    CASE

    Hi, ive designed an app using C# that uses a SQL Server 2005 Express database.

    I wish to deploy the app to 10 machines in our organization.  And place the database file on the companies network.

    I have set all firewall properties in accordance with this.

     

    PROBLEM

    If i install MS SQL Server management studio express on one of the ten pc's i am able to connect to the database

    using Management Studio.  All i do is supply the ip address user name and password. No problem there.

     

    HOWEVER, if i use my C# application to connect, it kicks an error Login failed for user \SQLEXPRESS\Gust.

    WHY IS THIS?  Can someone please correct my connection string for me.  id completely appreciate this.

     

    current string is

    Data Source=.\SQLEXPRESS;AttachDbFilename=C:\AMI\IStudio.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

     

    what am i doing wrong here???

     

    thanks

    Mohamed

     

     

    You seem to be using the Windows Guest account, which might not be a good idea over the long haul.  To use the Guest account you need to have that account enabled on the server and on each of your 10 PCs.  It might be better to create a separate account for each user on the server.

     

    Keep in  mind that each user needs to connect to Windows, and then the Server.  That's 2 accounts you need to setup.  Once connected to the server, you need to setup security and login on which database they need to connect to.  Once they are connected, you need to grant them priviliges to do stuff with that database.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2164297&SiteID=1

     

    Take a look at that post.  It describes connecting using Windows authentication, and how the Guest account fooled me into thinking that I had it working.  Then it describes how to connect using SQL Authentication.

     

    Rudedog

     

    Hello Rudedog,

     

    Maybe i'm stupid but in the connection string i've seen that user instance = true and Mohamed said that he wants to install an Sql Server instance on a pc and to use 10 other pc with on each pc an application needing to connect to the instance.

    I was thinking it's impossible to use user instance across a network. Am i foolish or it's true ?

    Moreover no Sql server authentification with user instances ( only Windows authentification )

    If user instance is impossible , in the connection string, must disappear the part with user intance = and AttachedDbFileName =

    But must exist Initial Catalog = databasename

     

    Sorry to seem to be rude, but it's maybe the key of the problem

     

    For me , the connectionstring must be built thru the SqlConnectionStringBuilder as you don't have multiple names for the same key of the connection string :

    SqlConnectionStringBuilder bu = new SqlConnectionStringBuilder();

    bu.DataSource = ".SQLEXPRESS;  // to change . in the name of the pc where the Sql Server instance resides

    bu.IntegratedSecurity = true;   // for Windows Authentification

    bu.InitialCatalog = databasename;

    SqlConnection conn = new SqlConnection();

    conn.ConnectionString = bu.ConnectionString;

    or ( i never used it )

    SqlConnection conn = new SqlConnection(bu.ConnectionString);

     

    If you please, Rudedog, could you tell me if i'm right or not ?

     

    Thank you very much and have all a nice day

    Thursday, March 6, 2008 5:42 PM
  • Papy,

    His message "...user failed to login..." could have come at a few different points in the login process, as descibed above.  If he goes through the steps to login, he will see the hurdles in the way.  He could connect successfully to the server and still get a similar message because he does not have a login or security privileges to specific database...and then a particular table.

     

    As for the string, I agree with doing away with the user instance stuff.  And you are correct about his string is trying to login to a local instance, not a named instance on a named machine.  I had not noticed that.  I was thinking of the numerous reasons to get that message.  I think you're hitting the nail on the head, though.

     

    Rudedog
    Thursday, March 6, 2008 6:23 PM
  • Rudedog2,

     

    I was thinking about these 10 pc and i'm wandering how they connected to the pc where the SQL Server instance resides.

    Normally, if they are on a domain network ( with a Windows server as controller ), there is no problem except that we must be sure that, thanks to Active Directory all the users are known on Sql Server PC and that they are known with all the necessary permissions on the instance and the necessary databases

     

    I'm thinking like you that's very complicated except if is defined a special group of users in Windows and that group is added inside the instance ( i know in french what i want to exprim, it's more difficult in english ). Maybe, you could well format that as it's your primary idea.

     

    But if the network is built around a workgroup, i fear it will be more difficult . I've discovered it at home with my workgroup with XP Pro and Home

     

    Have a nice day

     

     

     

    Thursday, March 6, 2008 7:12 PM
  • Hello,

     

    A refinment in case of a domain network :

     

    Can several different users connect on a same computer ?

     

    Have the users the same permissions on all the tables of the database ?

     

    What i mean, is if the user A have read/write access on table1 , read access on table2 and the user B has read/write access on table2 and no access on table1 but they may work on computers pc1 and pc2, and the user C must work on pc3 and nowhere else, it may be more simple to use Sql authentification instead Windows authentification.

    If it's possible to divide the users into groups with exactly the same permissions, it's possible to use windows groups and to add these windows group as windows users on the level of the Sql Server for which the specific access permissions are set.

     

    I think that may be a solution no simple to foresee but easier to applicate.

     

    Have a nice day

    Thursday, March 6, 2008 8:26 PM
  • Papy,

    I never noticed that English is not your first language before you said so. 

     

    The OP might want to check how many concurrent connections are permitted with SQL Express. 

     

    Connecting across a workgroup is what that link I provided in my first reply talks about.  There are no roaming profiles or user groups.  So, as you noted, the OP will have to setup the users one by one.  Setup would be required at each step of the connection from Windows to SQL Express to database and finally to tables.............for each user.

     

    Rudedog

    Thursday, March 6, 2008 8:59 PM
  • Papy & Rudedog2:

     

    I wish you genius' would look at my post titled "get database definitions in C#"

    Friday, March 7, 2008 2:35 AM
  •  greenhouse wrote:

    Papy & Rudedog2:

     

    I wish you genius' would look at my post titled "get database definitions in C#"

     

    Your post does not exist.  At least not on this forum.

    If you want someone to read it, please provide a link.

    I'm done searching for it.

     

    And your reply is just a little sarcastic and offensive.

    There is no need for that coming from right field.

    I'm sorry if you find our chat offensive or otherwise insulting.

     

    If you have something useful to contribute, then post it.

    Let me remind you that this is your first post on this thread. 

     

    There are many forum participants who find my nickname offensive.

    It appears that you are one of said types. 

     

    I will explain the nickname to you.

    My name is Rudy, and I was given the name because I am favorite with the ladies.

     

    Rudedog

    Friday, March 7, 2008 7:56 PM
  • Sorry. Absolutely NO offense intended. Actually the contrary, a compliment, was meant. Best.

    Friday, March 7, 2008 8:26 PM