Storing And Using Microsoft User Account Credentials in SQL Server 2008 database

views:

127

answers:

+1 Q:

Storing And Using Microsoft User Account Credentials in SQL Server 2008 database

I'm not exactly positive how to word this for the sake of the title so please forgive me. Also I can't seem to figure out how to even google this question, so I'm hoping that I can get a lead in the right direction.

Part of my software(VB.NET App) requires the ability to access/read/write a shared network folder. I have an option for the user to specify any credentials that might be needed to access said folder.

I want to store these credentials given in the SQL Server database as part of the config (I have a table which contains configuration).

My concern is that the password for the user account will be unencrpyted. Yet, if I encrypt the password the VB.NET App And/Or database will be unable to use the credentials for file i/o operations unless the Password is unencrypted before use.

I'm fishing for suggestions on how to better handle this situation.

I would suggest using Windows authentication in your application. If you do that, then you can set permissions on the shared folder and in the database and avoid storing credentials of any sort in the database.

Thomas 2010-04-26 17:14:08

You need to use your own encryption algorithm or one that allows you to decrypt the password again if you want to store it encrypted. There are many ways to do this one of the more common is to encrypt them with a safeword so you can use the same word to decrypt again.

Well, probably not a real word but something like a random byte-array or anything similar.

Jonas B 2010-04-26 17:16:05

SQL Server itself has means to store Windows credentials, see CREATE CREDENTIAL:

A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server. Most credentials include a Windows user and password.

But storing user credentials in the application, in any form, is always a bad idea. For one, they go out of sync when the user changes the password. Second, impersonating users to access resources is never the correct solution. The correct solution is to actually set up group membership and properly define access control lists on the resources.

The only correct scenarios for accessing resources are:

as the user running the application, ie. the normal run-of-the-mill use case
as a service that impersonates the connected user, which is done by obtaining a remote identity token from an SSPI authentication, like the NegotiateStream.RemoteIdentity

Remus Rusanu 2010-04-26 17:33:42

Thanking all of you for your answers, I think Windows Authenication will do nicely. If I can bug you for a little more info, the end-goal of this app is to have many users with different permissions(user group dependant). I'm understanding now, that I should allow the end-user application to access the specified network share, and specify user/group specific permissions in their Windows Account settings. Correct? Also, where do you think I should store application-specific permissions? Am I able to somehow specify these permissions the same way I would other windows permissions?

instantmusic 2010-04-26 20:08:30

You can allow access resources for specific users/groups. You can allow access to your application to specific users/group. What you cannot do is you cannot allow access to *resources* for a specific *application*. There simply isn't any infrastructure that allows you to authenticate *applications, as opposed to users.To allow access to a resource for an user or a group modify the resource ACL (Access Control List), either from the UI shell or by using a tool like cacls.exe.

Remus Rusanu 2010-04-26 20:47:33

Ahh I'm sorry, I think I might have misworded that slightly. I meant to say that the application will access the folder directly, what it can/cannot do to the folder will be dictated by the user account that is logged in and using the program.My next concern is custom application permissions(User has access to view this form/grid, user can add/edit/delete records from this grid, etc) but just some quick reading around it looks like I might have to store app-specific permissions in my DB, and map users/groups to app-specific permissions manually.That about right? I'm wide open for tips.

instantmusic 2010-04-26 20:59:27

I see. Unfortunately, there is no out-of-the-box module to handle this requirement. You pretty much have to roll your own, from scratch. For Web apps the Membership provider handles this for you, see http://msdn.microsoft.com/en-us/library/tw292whz.aspx. For forms, there is no built-in mechanism. Some have tried porting the ASP provider to forms, see http://msmvps.com/blogs/theproblemsolver/archive/2006/01/12/80905.aspx.

Remus Rusanu 2010-04-26 21:10:04

The basics of implementing authorization into an application goes like this: you define resources (eg. screens and operations), and you identify them by some id (eg a name). you provide a means to configure and to store access to resources: domain\user is granted access to form1, built-in\everyone is granted access to form2, machine\groupA is denied access to form1 etc. Then, in the applicaiton, you look at the current user token WindowsIdentity.GetCurrent(), enumerate the Groups and check to see if it should be granted access yes/no to the form/operation you are about to display/perform.

Remus Rusanu 2010-04-26 21:14:00

The access authorization rules go like this: a single DENY trump all GRANTS. So you must check *all* groups a user is member of. If any group is DENIED, then user is not authorized. If there are no DENYies and there is at least a gRANT, the user is authorized. IF there are no DENYies not GRANTs, the user is not authorized. Things get more messy/hairy/complicated if you have hierachies of access (eg. access to an operation because you granted access to a form).

Remus Rusanu 2010-04-26 21:17:21

Perfect, I've a little reading to do naturally but thats exactly what I'm looking for. Thank you much for your time!

instantmusic 2010-04-26 21:18:08

However, in practice there is a simpler model that most apps go with: the *role* model. You define a few roles, derived from the application use cases (eg. Administrator, Visitor, User, Editor). Then you add/remove users to the roles. Access to forms/operations is controlled by this fixed roles. You still need to check the current user token for group membership in code, but at least you don't have to provide a custom GRANT/DENY/REVOKE permissions language and hierarchy, you just allow for a fixed set of roles.

Remus Rusanu 2010-04-26 21:20:10

ansaurus

tags:

views:

answers:

Storing And Using Microsoft User Account Credentials in SQL Server 2008 database

related questions