none
Create vb.net user login form

    Question

  • Hi all,

    I made a Windows forms app using vb.net (VS2005) and SQL server 2005 Express Edition (to which I'm new). During the creation of the app I used windows authentication in the connection string. Now I want to secure the app, meaning I want the SQL Server database not to be accessed without a username/password. I guess the logical thing is to create a login form, providing the username and password so it can be used for connecting to the database. I thought this would be a standard (thus easy) thing to do, but no forum or article yet showed me a way to accomplish this in an understandable way. I also want to deploy this app using click once technology. I need to distribute this app on a cd/dvd.

    If possible, can anyone give me a step by step scenario for this? Many thanks in advance

    (ps: please let me know if this should be in another thread)

    Wednesday, July 19, 2006 12:51 PM

Answers

  • hi,

     Sjako wrote:

    ...

    This app will be used by a medical doctor who spends his days visiting his clients. He needs to make loggings of each visit, the activities carried out and the used or left behind medicins etc. and leave a report of these loggings (as prescribed by european law). So he'll be working with a notebook and a mobile printer. He will be making daily backups of the database (about which I have a question below) to a USB-stick. Anyone who will have access (in whatever legal/illegal way) to this notebook or USB-stick must be prevented from accessing the data.

    I do see additional "problems" in this scenario... you're dealing "sensible" data... a lot of this data should be encrypted (I'm not that fluent in this kind of requirements, but I do know our European law if far more "sensible" to this stuff than amercan is), but this is a builtin provided feature in SQLExpress as well... this will prevent access outside your application to the data (using tools other then you app), and accessing the usb stick by "illegal users" to use the backups is still a protected scenario for encrypted part of the data... and of course all this is another thread worth

    but you still have to  "manage" your security path... if you do not "trust" the integrated security path as Mike defined, say becouse the doctor is lazy and never "disconnect" the lap top so that everyone in the room can use the app, you should go the other way, standard SQL Server logins, or perhaps a third way as well, using "application role", as explained and reported in http://msdn2.microsoft.com/en-us/library/ms190998.aspx, in it's pure implementation or "sort of"..

    the "sort of" design require you to access the database(s) with a "hard coded" login (even hard coded in the application), so that the actual database user always is this principal, but you can validate the userid+pwd you request in you app against a user table (hopefully with encrypted data as well) so that data access is granted/validated against your logic design... this will not prevent user accessing data from, say, SQL Server Management Studio Express to access the database, but the encryption layer you impose on part of it (the sensible part of it) should protect data accordingly to requirements...

     Sjako wrote:

    Mostly the app will be used by one person, but sometimes a colleague will use the app too (on the same machine). At no time there will be multiple users at the same time working in the database. Perhaps in future more of his colleagues will be wanting to use this application, so that's why I thought it should be distributed on cd/dvd. Mike, I guess the answer to your question about whether the database should be deployed as part of my application or not should be 'yes'?

    I do think that this kind of scenario is the one for User Instances.. single user approach and all the rest...

    http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp

    I only see problems if you like to sync data with a "general" storage instances where all "doctors" will submit dayly activities when they come home in the evening.. but this can be done as well.

     Sjako wrote:
    At this point the application is ready for deployment except for these topics.

    not a little aspect of..

     Sjako wrote:
    Here are my questions:

    1. Is making a back up of the database as easy as copying the .mdf? What would you recommend concerning the app I described?

    if going through User Instances, backing up databases files can be ok, but in "traditional" scenarios I'd recommend a "traditional" backup via SQL Server provided syntax...

     Sjako wrote:
    2. In SQL Server Management Studio I noticed that when connecting to SQLServer using Windows Authentication I don't have to submit a password (as Andrea pointed out because I am the local administrator). Does using Windows authentication mean that from the moment the user started up Windows with his username/password that has been granted access to the database and SQL Server instance, he automatically has acces to the database without having to log in furthermore at the moment he wants to use the app? I'm asking this because almost every apllication you can buy, ie. for administration tasks, asks for a login name and password. I don't want to reinvent the wheel, I just want to do things in a way it is commonly done because it will probably be a good way. Perhaps these logins are seperate form the database and only provide access to the application? If so, that would be kind of like what Andrea mentioned (quote) "...so, if you want to save in a "user table" a corresponding set of "Users"+ "Password" you are actually "duplicating" builtin features already implemented in the engine.....", since providing access to the application without providing access to the database would be meaningless (at least in case of this app). Mike, in your first paragraph you mentioned users not having to sign in as an advantage and to me this seems logical, but why then are all those apps out there asking to sign in when the app is launched?  

    yes, integrated security means that you are first validated at OS boot, then only your Windows account's sid is passed to SQL Server at SQL Server connection time for validation... if a corresponding SQL Server (based on Windows NT account/group) exists, you can go on  withour further hassles...

     Sjako wrote:
    3. The links Andrea gave (almost) all describe doing things in a programmatical way using T-SQL. Is it true that some of these things, like CREATE LOGIN, CREATE USER and GRANT...TO can also be done manually (but statically) in SQL Server Management Studio? (select security, logins, right-click, new login etc.) If so, which way would be recommended for my app?

    yes, these tasks can be performed via SSMSE as well... depending on how you will go on with your security/authentication path you have to determine how to perform them..

    IMVH, anyway, usually, where no IT staff is present, your app should mimic SSMS behaviour, so you should provide a dialog to add/manage users (which implements both SQL Server logins and database users) as long as feature's permissions (where you grant specific users access to specified features of your app/data)..

    in a simple scenario, using SQL Server provided security path, where you have, say, 5 features (manage patients ID's, prescribe medicines, print out data, manage permissions, manage patient's history) you can implement 5 related database roles (1 for each task) and make the database users member of the database role they have permissions on

    so you can grant database user Andrea member of ManagePatientsID and PrintData roles, while you can be member of PrescribeMedicines and ManagePatientsHistory and the doctor member of all roles..

    you can then check the IS_MEMBER('ManagePatientsHistory') return value (http://msdn2.microsoft.com/en-us/library/ms186271.aspx) to see if the connected user is legitimated to access the specified feature, and obviously you grant data objects permissions at database role level...

    with the Application Role design, you have to manage this your self, with auxiliary table(s) that maintain permissions... and you have to query those tables to resolve permissions before granting access to a specific feature of your app...

     Sjako wrote:
    4. If using the T-SQL commands mentioned above, should they be executed every time the app is launched, or are the logins, users and grants saved in the database/SQL Server instance? Are stored procedures the best way to execute these T-SQL commands (or what would you recommend)? 

    only at [database]/[security schema] creation time... logins are saved in system databases, users are saved in the user database(s) as long as database permissions..

     Sjako wrote:
    5. The .mdf that is connected to the app doesn't show up in the Object Explorer in SQL Server Management Studio and I cannot find a way to access it via SQL Server Management Studio. Is the reason for this that it is stored in a map that is a submap of My Documents? If so, what is the reason for this? (The .mdf was created by adding a database to the project in VS2005).  

    the reason is the use of User Instances.. see the link already provided...

    all security related opinions obviously are "opinions"

    regards

    Friday, July 21, 2006 2:36 PM
    Moderator

All replies

  • hi,

     Sjako wrote:
    I made a Windows forms app using vb.net (VS2005) and SQL server 2005 Express Edition (to which I'm new). During the creation of the app I used windows authentication in the connection string. Now I want to secure the app, meaning I want the SQL Server database not to be accessed without a username/password. I guess the logical thing is to create a login form, providing the username and password so it can be used for connecting to the database. I thought this would be a standard (thus easy) thing to do, but no forum or article yet showed me a way to accomplish this in an understandable way.

    authentication is first checked at server connection, where, in your case, you are using Windows integrated security... this is the very first phase of the authentication process supported by SQL Server.. a login (both Windows and/or standard SQL Server with userid and pwd) must exists in order to allow "someone" to connect to a SQL Server instance... in your case you can log in as SQL Server by default grants local administrators (builtin\administrators) to connect, making them member of the "sysadmins" server role... further "user" must be manually "added".. that's to say you have to grant yourself "builtin\users" Windows NT group and the like to grant local limited Windows user connection permissions.. this is performed via the provided syntax, CREATE LOGIN .......;, where you actually are granting someone, a Windows NT user/group, a standard SQL Server login, connection permissions to the current instance.. (http://msdn2.microsoft.com/en-us/library/ms189751.aspx)..

    you can then make them members of other server roles, depending on your needs/requirements...

    the 2nd phase regards database access... that's to say anyone who has been granted connection privilege to a specified SQL Server instance, must be granted permissions to access a specified database, as only members of the SQL Server sysadmins server role can access whatever database without explicit permission..

    this is performed creating a CREATE USER...; statement for each required database, where you are this way mapping a database user to a specified login.. (http://msdn2.microsoft.com/en-us/library/ms173463.aspx).. additionally, a sery of database roles are already available when a dabatabase is created, as additionally permissions are required to perform specific tasks on database objects like table, views, procedures and so on..

    so, at least, a login object and a database user object (mapped to the corresponding login) are required to grant someone both connection to the server instance and to a specified database.. more about this at http://msdn2.microsoft.com/en-us/library/ms187648.aspx

    the lowest level in this authentication chain are, of course, permissions at the database user level.. as already pointed out, a database user must be grant permissions to access database objects such as tables, views, procedures and so on.. this is performed via the GRANT <permission> TO ...; (http://msdn2.microsoft.com/en-us/library/ms187797.aspx) where specific privileges are granted/denied/revoked to a specific principal, in our case a database user..

    what you are required to is:

    - create/manage logins;

    -create/manage database users

    -manage permissions at user's level... this very last section is usually perfomed managing them at a more higher level, as Windows OS do their self, via omogeneus groups of related users... you can this way create a database "Management" group, a "Sales_Persons" group, a "Call Center" group (or whatever depending on the data/application you are designing) and provide every login->database_user is mapped to the corresponding required database group.. you then manage objects permission (SELECT, INSERT, UPDATE, DELETE, EXECUTE, ....) at this granulartity..

    you grant then

    GRANT SELECT, INSERT, UPDATE, DELETE ON schemaName.objectName TO Management;

    (or even at the schema level and not directly at the object level) to grant CEO,CIO and the like to perform whichever DML task on the Payroll table(s) as they will be able (and require to) access this type of information, but you only

    GRANT SELECT ON schemaName.objectName TO Sales_Persons;

    to Sales Persons as they are not supposed to modify but only view data from the specified table...

    this is how it generally works... so, if you want to save in a "user table" a corresponding set of "Users" + "Password" you are actually "duplicating" builtin features already implemented in the engine, and of course you are probably not duplicating them the correct/complete way.. all this, IMVHO and concerning engine features only...

     Sjako wrote:

     I also want to deploy this app using click once technology. I need to distribute this app on a cd/dvd. 

    "click once" and cd/dvd are not good friends  but you can get more info about that on Visual Studio forums..

    regards

    Wednesday, July 19, 2006 2:58 PM
    Moderator
  • Hi,

    Windows Authentication is the recomended way to secure your database, if it is available to you, you should stick with it rather than going to SQL Authentication. The primary thing that recomends Windows Auth is that you don't need to have users sign in since thier windows credentials are automatically used.

    As pointed out already, securing you database has to do with the creation of Logins and Users. I won't belabor the point beyond reiterating that the default installation of SQL Express sets up permissions for the build in administrators account as a SysAdmin. We also create a login for Builtin\Users, but there are no specific database users created for this Login.

    How you set up Logins and Users will likely be fairly specific to your situation, and could be different for each installation depending on whether your application is being deployed within your own company, where you know the Windows security structure, or at other companies where you don't know anything about how they control access. You should read all the BOL topics on security to get a handle on how to set this up. (Links in the previous post are good starting points.)

    Finally I wanted to comment on ClickOnce deployment. ClickOnce deploys application files to a per user file location designed to work for non-administrative users. You did not mention if you intend to deploy your database as part of your application or not. If you do, there are some issues you need to be aware of.

    • Database access from a ClickOnce installed location is typically only possible through the use of User Instances. User Instances will only work for single user access to the database, no shared access is possible. If you need to have multiple users in your database, you can use ClickOnce for your application, but not your database file.
    • There are some problems with deploying database files using ClickOnce when you are upgrading your applications. Check out my embedded database webcast and the embedded database white paper for more information.

    Hope this helps.

    Regards,

    Mike Wachal
    SQL Express team

    ----
    Mark the best posts as Answers!

    Thursday, July 20, 2006 5:55 PM
    Moderator
  • Hi Mike, Andrea,

    Thank you both for your replies. From both of them I can conclude that I'll have to learn a lot more about these topics, but this info will be a good starting point. Since I'm pretty new to .Net and MSSQL Server -this is my first .Net application, I made lots of applications in Access for professional purposes- I'm trying to find a way through all the information about securing and deploying MSSQL databases. Please let me explain some more about the app I created, because, if I may, I would like to ask you some more concrete questions.

    This app will be used by a medical doctor who spends his days visiting his clients. He needs to make loggings of each visit, the activities carried out and the used or left behind medicins etc. and leave a report of these loggings (as prescribed by european law). So he'll be working with a notebook and a mobile printer. He will be making daily backups of the database (about which I have a question below) to a USB-stick. Anyone who will have access (in whatever legal/illegal way) to this notebook or USB-stick must be prevented from accessing the data. Mostly the app will be used by one person, but sometimes a colleague will use the app too (on the same machine). At no time there will be multiple users at the same time working in the database. Perhaps in future more of his colleagues will be wanting to use this application, so that's why I thought it should be distributed on cd/dvd. Mike, I guess the answer to your question about whether the database should be deployed as part of my application or not should be 'yes'? At this point the application is ready for deployment except for these topics. Here are my questions:

    1. Is making a back up of the database as easy as copying the .mdf? What would you recommend concerning the app I described?
    2. In SQL Server Management Studio I noticed that when connecting to SQLServer using Windows Authentication I don't have to submit a password (as Andrea pointed out because I am the local administrator). Does using Windows authentication mean that from the moment the user started up Windows with his username/password that has been granted access to the database and SQL Server instance, he automatically has acces to the database without having to log in furthermore at the moment he wants to use the app? I'm asking this because almost every apllication you can buy, ie. for administration tasks, asks for a login name and password. I don't want to reinvent the wheel, I just want to do things in a way it is commonly done because it will probably be a good way. Perhaps these logins are seperate form the database and only provide access to the application? If so, that would be kind of like what Andrea mentioned (quote) "...so, if you want to save in a "user table" a corresponding set of "Users"+ "Password" you are actually "duplicating" builtin features already implemented in the engine.....", since providing access to the application without providing access to the database would be meaningless (at least in case of this app). Mike, in your first paragraph you mentioned users not having to sign in as an advantage and to me this seems logical, but why then are all those apps out there asking to sign in when the app is launched?  
    3. The links Andrea gave (almost) all describe doing things in a programmatical way using T-SQL. Is it true that some of these things, like CREATE LOGIN, CREATE USER and GRANT...TO can also be done manually (but statically) in SQL Server Management Studio? (select security, logins, right-click, new login etc.) If so, which way would be recommended for my app?
    4. If using the T-SQL commands mentioned above, should they be executed every time the app is launched, or are the logins, users and grants saved in the database/SQL Server instance? Are stored procedures the best way to execute these T-SQL commands (or what would you recommend)? 
    5. The .mdf that is connected to the app doesn't show up in the Object Explorer in SQL Server Management Studio and I cannot find a way to access it via SQL Server Management Studio. Is the reason for this that it is stored in a map that is a submap of My Documents? If so, what is the reason for this? (The .mdf was created by adding a database to the project in VS2005).
    6. What is the alternative to ClickOnce, since I don't see any other option in the properties of the project. VS 2005 automatically uses ClickOnce from what I've seen so far. (forgive me for being this new to .Net)
    7. Overall, any recommendation you would have for me considering this particular app is welcome.

    I can understand if some questions are hard to answer in a concrete way because, as always, a lot of things depend on a lot of things that I may not have mentioned. Still I'd much appreciate it if you could describe how you should do it (and why), knowing what I mentioned about this particular app.

    Regards,

    Sjako

    ps: you might not believe it, but the rest of the app is working great!

    Friday, July 21, 2006 10:25 AM
  • hi,

     Sjako wrote:

    ...

    This app will be used by a medical doctor who spends his days visiting his clients. He needs to make loggings of each visit, the activities carried out and the used or left behind medicins etc. and leave a report of these loggings (as prescribed by european law). So he'll be working with a notebook and a mobile printer. He will be making daily backups of the database (about which I have a question below) to a USB-stick. Anyone who will have access (in whatever legal/illegal way) to this notebook or USB-stick must be prevented from accessing the data.

    I do see additional "problems" in this scenario... you're dealing "sensible" data... a lot of this data should be encrypted (I'm not that fluent in this kind of requirements, but I do know our European law if far more "sensible" to this stuff than amercan is), but this is a builtin provided feature in SQLExpress as well... this will prevent access outside your application to the data (using tools other then you app), and accessing the usb stick by "illegal users" to use the backups is still a protected scenario for encrypted part of the data... and of course all this is another thread worth

    but you still have to  "manage" your security path... if you do not "trust" the integrated security path as Mike defined, say becouse the doctor is lazy and never "disconnect" the lap top so that everyone in the room can use the app, you should go the other way, standard SQL Server logins, or perhaps a third way as well, using "application role", as explained and reported in http://msdn2.microsoft.com/en-us/library/ms190998.aspx, in it's pure implementation or "sort of"..

    the "sort of" design require you to access the database(s) with a "hard coded" login (even hard coded in the application), so that the actual database user always is this principal, but you can validate the userid+pwd you request in you app against a user table (hopefully with encrypted data as well) so that data access is granted/validated against your logic design... this will not prevent user accessing data from, say, SQL Server Management Studio Express to access the database, but the encryption layer you impose on part of it (the sensible part of it) should protect data accordingly to requirements...

     Sjako wrote:

    Mostly the app will be used by one person, but sometimes a colleague will use the app too (on the same machine). At no time there will be multiple users at the same time working in the database. Perhaps in future more of his colleagues will be wanting to use this application, so that's why I thought it should be distributed on cd/dvd. Mike, I guess the answer to your question about whether the database should be deployed as part of my application or not should be 'yes'?

    I do think that this kind of scenario is the one for User Instances.. single user approach and all the rest...

    http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp

    I only see problems if you like to sync data with a "general" storage instances where all "doctors" will submit dayly activities when they come home in the evening.. but this can be done as well.

     Sjako wrote:
    At this point the application is ready for deployment except for these topics.

    not a little aspect of..

     Sjako wrote:
    Here are my questions:

    1. Is making a back up of the database as easy as copying the .mdf? What would you recommend concerning the app I described?

    if going through User Instances, backing up databases files can be ok, but in "traditional" scenarios I'd recommend a "traditional" backup via SQL Server provided syntax...

     Sjako wrote:
    2. In SQL Server Management Studio I noticed that when connecting to SQLServer using Windows Authentication I don't have to submit a password (as Andrea pointed out because I am the local administrator). Does using Windows authentication mean that from the moment the user started up Windows with his username/password that has been granted access to the database and SQL Server instance, he automatically has acces to the database without having to log in furthermore at the moment he wants to use the app? I'm asking this because almost every apllication you can buy, ie. for administration tasks, asks for a login name and password. I don't want to reinvent the wheel, I just want to do things in a way it is commonly done because it will probably be a good way. Perhaps these logins are seperate form the database and only provide access to the application? If so, that would be kind of like what Andrea mentioned (quote) "...so, if you want to save in a "user table" a corresponding set of "Users"+ "Password" you are actually "duplicating" builtin features already implemented in the engine.....", since providing access to the application without providing access to the database would be meaningless (at least in case of this app). Mike, in your first paragraph you mentioned users not having to sign in as an advantage and to me this seems logical, but why then are all those apps out there asking to sign in when the app is launched?  

    yes, integrated security means that you are first validated at OS boot, then only your Windows account's sid is passed to SQL Server at SQL Server connection time for validation... if a corresponding SQL Server (based on Windows NT account/group) exists, you can go on  withour further hassles...

     Sjako wrote:
    3. The links Andrea gave (almost) all describe doing things in a programmatical way using T-SQL. Is it true that some of these things, like CREATE LOGIN, CREATE USER and GRANT...TO can also be done manually (but statically) in SQL Server Management Studio? (select security, logins, right-click, new login etc.) If so, which way would be recommended for my app?

    yes, these tasks can be performed via SSMSE as well... depending on how you will go on with your security/authentication path you have to determine how to perform them..

    IMVH, anyway, usually, where no IT staff is present, your app should mimic SSMS behaviour, so you should provide a dialog to add/manage users (which implements both SQL Server logins and database users) as long as feature's permissions (where you grant specific users access to specified features of your app/data)..

    in a simple scenario, using SQL Server provided security path, where you have, say, 5 features (manage patients ID's, prescribe medicines, print out data, manage permissions, manage patient's history) you can implement 5 related database roles (1 for each task) and make the database users member of the database role they have permissions on

    so you can grant database user Andrea member of ManagePatientsID and PrintData roles, while you can be member of PrescribeMedicines and ManagePatientsHistory and the doctor member of all roles..

    you can then check the IS_MEMBER('ManagePatientsHistory') return value (http://msdn2.microsoft.com/en-us/library/ms186271.aspx) to see if the connected user is legitimated to access the specified feature, and obviously you grant data objects permissions at database role level...

    with the Application Role design, you have to manage this your self, with auxiliary table(s) that maintain permissions... and you have to query those tables to resolve permissions before granting access to a specific feature of your app...

     Sjako wrote:
    4. If using the T-SQL commands mentioned above, should they be executed every time the app is launched, or are the logins, users and grants saved in the database/SQL Server instance? Are stored procedures the best way to execute these T-SQL commands (or what would you recommend)? 

    only at [database]/[security schema] creation time... logins are saved in system databases, users are saved in the user database(s) as long as database permissions..

     Sjako wrote:
    5. The .mdf that is connected to the app doesn't show up in the Object Explorer in SQL Server Management Studio and I cannot find a way to access it via SQL Server Management Studio. Is the reason for this that it is stored in a map that is a submap of My Documents? If so, what is the reason for this? (The .mdf was created by adding a database to the project in VS2005).  

    the reason is the use of User Instances.. see the link already provided...

    all security related opinions obviously are "opinions"

    regards

    Friday, July 21, 2006 2:36 PM
    Moderator
  • I won't add much more to Andrea's detailed reply. (Go Andrea!)

    I've posted a bunch of stuff about User Instances in this forum that you might find useful as suplimental reading to the white paper:

    I would also encourage you to check out the webcast I did about deploying databases using ClickOnce technology. There are some tricks to learn if you ever want to update your application without overwriting your database.

    As Andrea points out, you'll want to look at encryption to deal with the "lost database" scenario. You can start your investigation into encryption with this topic in Books Online. The reason the lost database scenario can cause problems goes back to the default security settings. If you don't modify the default Logins on your database, any machine administrator will be a SysAdmin in the database. This means that if I can get your .mdf file and attach it to my SQL Server, I'm now an admin and I can look at all you data. Encryption adds another layer of protection because even if I'm an admin, I need to have the right key to decrypt your data.

    This may all seem like a lot of work for a little application, but we're talking about medical records and you need to do the right thing to ensure those stay private.

    Regards,

    Mike

    Saturday, July 22, 2006 1:54 AM
    Moderator
  • Okay guys, thank you both for your help. I'm going into study now,

    Regards,

    Sjako

    Saturday, July 22, 2006 5:43 AM