application create SQL logins?

Question

application create SQL logins?

Jane Matheson-154829

SSCertifiable

Points: 5522
More actions
November 28, 2005 at 1:19 pm

#90934

I am faced with a design of an application that will require SQL logins for users. The need to use SQL logins is not in question for this particular app.
I want to automate the creation of the logins for the application without granting securityadmin (and of course not SysAdmin). In the application, when a new user is added, I want the code to be able to call a SP to create a new SQL Login, grant it access to the database in question only and grant it a db role. Then it will have to encrypt the password and store it along with the account in another table - the user will never see this account. I can create a SP to do everything I want, but I'm hung up on the security.
I've found a reference (http://www.sypron.nl/grant_sa.html) recommends using application roles to temporarily give the connection the privileges needed. I don't see how this is much different than executing a statement as a different user (with password included) to add the login.
Please, any ideas and thoughts are appreciated as I am stuck given the lack of good choices. I'm using SQL 2000 SP4.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

Here is what I do: I create a restricted web server login that can SELECT from and INSERT into *Request tables (Password and Account), then insert the desired information into the "stage" tables. Always validate every field's value for SQL Injection before INSERT.

A scheduled stored procedure is used to process the contents of the "stage" tables, deleting or archiving the processed rows. Since you have control of the account the scheduled task runs under, no need for 'sa' permissions from the web site, or user machine, ever.

My stored procedure always emails the user to confirm that an account operation was performed. You could add BCC for monitoring.

I have this creating loads of additional tables (the account process inserts into 10 tables). I have order info request, info request, etc.

I believe in performing a Request lookup before insert to keep flooding issues at bay. I do not allow more that one queued item per entity at a time. For Account Requests I use an additional safety feature of a custom "hash" column that will reject any insert attempts not from my application.

BTW, this is done from an anoymous access web site, SSL of course, hence the restrictions.

Andy

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 2

Thanks for the feedback. It looks like I will end up with a process very similiar to yours. Perform the login creation disjoint in a scheduled job separate from the application using a scheduled stored procedure (job).

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

Just one addition. If your applcation requirements dictate a rapid creation of these logins then a regularly scheduled tasks may not be fast enough (e.g. every 5 minutes). One could also use:

sp_start_job

Instructs SQL Server Agent to execute a job immediately.

Syntax

sp_start_job [@job_name =] 'job_name' | [@job_id =] job_id

[,[@error_flag =] error_flag]

[,[@server_name =] 'server_name']

[,[@step_name =] 'step_name']

[,[@output_flag =] output_flag]

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

When you're able to upgrade to SQL Server 2005, you'll find you can get granular with the permissions and create the mix that you want. Ability to create a login, ability to grant access to a single database, and ability to add to a role within that database.

And as for sp_start_job, keep in mind the only way the login is going to be able to execute this for the job in question is if one of three things are true:

1) login owns the job (which will run in a particular context - see below)

2) login is a member of the sysadmin fixed server role

3) login's user mapping in msdb is a member of the database role TargetServersRole (unsupported)

If the login isn't a member of the sysadmin fixed server role, it'll attempt to execute under the context of the SQL Server Agent proxy account, if that has been set. As long as that login has the appropriate rights, you're all set. However, if the reason sp_start_job can be executed is due to the fact that the login owns the job, that also means the login could potentially modify the job to grant access to any database.... which is a reason for keeping this uncoupled.

K. Brian Kelley
@kbriankelley