none
Best way to configure connection params in client side RRS feed

  • Question

  • Hi folks!

    My current application has this characteristics:
    - A server running sql server, only.
    - Clients/users, connecting remotely to this server to access and send datas.. the usual.
    - There are two kinds of users, admins and normal users. The admins know the sql server login and password (connection string), the users don't.

    The problem:
    - Each client application needs the connection string, to connect into the sql server. So, what is the best way to configure this string for users clients. The admin should go client to client and configure the connection string? Or there is a way that users clients can connect to the server, no matter the login/password of the server? (the user should not have access to the tables and contents of the sql server without the client software, like using sql server management).

    Hope my problem is clear.
    Appreciate any help and tips.

    Thanks.
    Thursday, February 4, 2010 10:49 AM

Answers

  • Integrated login = Windows login = network login.  The beauty here is that the .NET rentime, when you tell it to use integrated login, it will pick up each users' username.

    If you can't do that, then you need to put the credentials into the config file, and deploy it to everyone.  If security is a concern, then encrypt it (read MSDN for how to do this).

    Generally, you use the DNS service and the server name, so if the IP address changes, then the DNS cache and browse lists will update, and you don't have to change anything anywhere other than the server.  Since you have specified a 192.168 address, you're inside your network, so:

    1. You should be able to use integrated logins which is the simplest and most secure way forward;
    2. You will need to set up the server to your server name and lookup for 192.168.1.10, you can do this through LMHosts file, but DNS service is the better way to go.  If the machine name changes (which would be a bit of a pain, and shouldn't happen often), you can update the connection string in the config file (you put it under <configuration>/<connectionStrings><add blah blah blah off the top of my head - you will need to look up the exact syntax).  This update is simple, although upgrading everyone will probably cause you not to change server names often.

    This isn't a complicated or uncommon scenario, but the idea of everyone having the same SQL login may create problems for you later down the track if you want to do anything with security for each user of the application. 

    If you don't understand, please do some searches, as I suspect that the fact that you're asking means you're not aware what your server, or your programming and hence application is capable of.  This really is a simple scenario.

    Hope that helps,

    Martin.

    MCSD, MCTS, MCPD. Please mark my post as helpful if you find the information good! http://www.consultantvault.com
    • Marked as answer by Rafael Pacheco Friday, February 5, 2010 11:08 AM
    Friday, February 5, 2010 10:41 AM

All replies

  • There might be better solutions but one that I can think off from the top of my head is provide some sort of lookup service.  Obviously you'd have to build in some security around that.
    Thursday, February 4, 2010 6:15 PM
  • Depends on the application - can you use integrated logins?  If you can then the connection string is the same for everyone, and you also have a finer grain of control over your users, than one user and password for all.

    If you can't do this, and you have to have a SQL login, then you can put it in the app.config file, and set it to be encrypted, but that's still not that secure, if that's an issue.  The above is much better.

    Hope that helps,

    Martin.
    MCSD, MCTS, MCPD. Please mark my post as helpful if you find the information good! http://www.consultantvault.com
    Friday, February 5, 2010 9:34 AM
  • I don't know what you mean about integrated login, but the string is the same for everyone (they all use the same user+pass to connect to the database, but only the admins know this user+pass).

    After the connection is established, then the user will send its username+password to the Users tables, trying to login in the application.

    The problem is, how to set (and later change) this connection string for all the clients, and if there are another better way to do this.

    In example:

    I sell my application and the customer said to me that the sql server will have the ip: 192.168.1.10
    Ok, so I build the application using this ip in the connection string, also the application has an UI to change this ip later (let's not talk about config saving and encryption for now).
    After that, i send to all clients this application, and they all connect fine to the server.

    But, the server has to change its IP, and now? Go client by client and setup the new ip address? (and only the admin can do that, not the normal users)!
    Even if i use the computer Domain as address, it can be changed too.

    Maybe I'm not seeing a better abstraction to this situation..

    Thanks for your tips, appreciate any other tips
    Friday, February 5, 2010 10:33 AM
  • Integrated login = Windows login = network login.  The beauty here is that the .NET rentime, when you tell it to use integrated login, it will pick up each users' username.

    If you can't do that, then you need to put the credentials into the config file, and deploy it to everyone.  If security is a concern, then encrypt it (read MSDN for how to do this).

    Generally, you use the DNS service and the server name, so if the IP address changes, then the DNS cache and browse lists will update, and you don't have to change anything anywhere other than the server.  Since you have specified a 192.168 address, you're inside your network, so:

    1. You should be able to use integrated logins which is the simplest and most secure way forward;
    2. You will need to set up the server to your server name and lookup for 192.168.1.10, you can do this through LMHosts file, but DNS service is the better way to go.  If the machine name changes (which would be a bit of a pain, and shouldn't happen often), you can update the connection string in the config file (you put it under <configuration>/<connectionStrings><add blah blah blah off the top of my head - you will need to look up the exact syntax).  This update is simple, although upgrading everyone will probably cause you not to change server names often.

    This isn't a complicated or uncommon scenario, but the idea of everyone having the same SQL login may create problems for you later down the track if you want to do anything with security for each user of the application. 

    If you don't understand, please do some searches, as I suspect that the fact that you're asking means you're not aware what your server, or your programming and hence application is capable of.  This really is a simple scenario.

    Hope that helps,

    Martin.

    MCSD, MCTS, MCPD. Please mark my post as helpful if you find the information good! http://www.consultantvault.com
    • Marked as answer by Rafael Pacheco Friday, February 5, 2010 11:08 AM
    Friday, February 5, 2010 10:41 AM
  • Thanks Martin!

    I'll use Integrated Login, this is really an easy way to connect to the sql server
    Also, I'll not use DNS server, since the application can remotely connect to the server too (outside the local network, also i have to learn how to setup a dns to the local network).
    Then, the only thing the user will have to know is the server ip address, witch is not a problem.

    For the outside users i'll have to create a "global" user/login (supose that integrated don't work outside network)

    So, the problem now is:
    - the application can delete some tables, but how to avoid the user to not use the SQL Server Management and be able to delete those tables manually?

    Maybe this is an off-topic, but thanks for your help, i'll use it!

    Rafael Pacheco
    Friday, February 5, 2010 11:07 AM
  • Sounds like a plan!

    Just note that connecting remotely would still require a login to the network, and that the DNS server can also be configured to work outside of your work network (ask your network admin)  That's still the easiest way, but the IP address if it doesn't change much, shouldn't be a problem.  However, also note that the IP address you supplied (192.168.10.1 or whatever it was) will not work outside of your network, as it is what is called a NAT address, which means the router assigns an IP address to be used internally where that address would be seen outside of the network as the IP address of the gateway.  To make it externally available, you would need to get the database into the DMZ (on the 'net).

    With integrated logins, you can add users to a role, and grant or deny permission for create / read whatever on the database tables.  However, you can't prevent a user for doing something when logging in via your app, as apposed to logging in with SSMS, but then again, it is very bad practice to allow a user to delete anything in the database.  If you need to do that, you should make them call a stored procedure (which you should be doing for both performance and security reasons)  and then the stored procedure can delete the tables on their behalf, but that's because it's a business rule of sorts.  By the way, why would you let a user delete a table?

    Glad I could help.

    Martin.
    MCSD, MCTS, MCPD. Please mark my post as helpful if you find the information good! http://www.consultantvault.com
    Friday, February 5, 2010 11:16 AM
  • Hi Martin!

    About the ip.. it was just a sample, i know about the NAT address and that is not a valid WAN ip ;)

    Ok, back to the login issue:

    My mistake, i was talking about delete the table contents, not the table itself
    The application though LINQ to Entity call some DeleteObjects, the usual.
    So, the problem is, if the application can delete those contents, so the user using SSMS can do the same right? But using SSMS the user will be able to delete stuffs from other users, and of course this if not allowed (by the business rule).

    Then, you said about Stored Procedures.. so far I was using LINQ to manipulate the database inside the application (read, update, delete). Should I change them all to stored procedures? This will avoid the user to delete table contents using SSMS?

    Thanks for your help and tips!

    Rafael Pacheco
    Friday, February 5, 2010 12:01 PM