Security Admin Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Security Admin Issue

Author

Topic

vbwizard
Starting Member

12 Posts

Posted - 2007-10-18 : 14:56:36

On the app I am developing I have created "self-healing" stored procedures. If the stored procedure does not exist due to failure of the backup to restore it or for some other "odd" reason, the code recreates on the fly then executes it. This is handy cause I can admin my stored procedures in my code and in debug mode they get created before they are executed automatically. To accomplish this i require a user to have essentially sa permissions. Of course this is bad practice in anything but a single user (sqlexpress,msde) environ. Now that I have brought this code to an enterprise I am attempting to create a user that has permissions to create stored procedures and little else. No luck so far.

Here is what I have done to date:
1. Created a new user called procedure_admin with a SQL Server login password. This account is NOT authenticated through the domain.
2. The user is automatically in the public role and I cannot drop it from that role.
3. Gave the user db_owner permission (as this is what I do at home with this code)
... this did not work, I got "CREATE PROCEDURE permission denied in database 'test'."
I double checked, the db_owner is selected for this user under the 'test' database. So then I trundle over to Server Roles for this user and selected Setup Administrators as the role. This may be a custom role on this server I do not know. It is described as being able to Manage extended Stored Procedures. no go.. did not work.. so I gave in and tried to give the user System Administrators role and I still get the same error when trying to execute the procedure create script...

Does anyone have any tips for me that would assist. I tried searching the forum and I did find a post where someone was wondering why there is not a specific role for sp creation and someone said that this was db_owner but this is not working for me.

Question: Is there a way to lockout procedure creation so that they can only be created by NTLM authenticated users?

You should check out www.hogwarts.tv ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-18 : 15:01:00

quote:

If the stored procedure does not exist due to failure of the backup to restore it or for some other "odd" reason, the code recreates on the fly then executes it.

This is absurd. You aren't performing backups correctly if any of your objects are missing upon a restore.

If what you are doing is only in a dev environment, then I have no problems with it. However it you have these self-healing stored procedures in production, then I whole-heartedly disagree with this. Security and performance would be my concerns in production.

db_owner is all you need to create dbo objects in a database. sa/sysadmin is not needed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

vbwizard
Starting Member

12 Posts

Posted - 2007-10-18 : 15:15:02

The code does check to see if it fact it *must* create the procedure first using sp_stored_procedures and checking to see if it exists.

I also designed this so that I don't have to deploy stored procedures I don't use. The code generator that I wrote writes all the Data Access Layer code for me so I can concentrate on business logic and UI. I don't have a DBA for my own projects, I am the DBA for my own projects and frankly I find writing repetive stored procedures tedious, so I wrote a code generator to handle it for me. It queries the database and based on a preset of rules it goes in and creates the code.... The self-healing part is so that when I deploy a project based on a DLL created from the code generator, it only creates those procedures it needs and no more. (this is a nod to the security folks, if the app never calls the code to access a given stored proc, there is no way to create it as the DLL requires authentication so you can't just create your own line into the dll and run all the controller procedures)

Yes, I agree, the correct way would be to analyze what I need and only deploy what I need, I don't have time to be quite that strict. So.. in my context, doing this is not absurd, but rather an excercise in time management.

The problem still remains. I have created a user with the db_owner abilities and this user cannot create stored procedures when called from Visual Basic .NET. code. I have verified that the connection string being used contains the full and working credentials of the sql user...

In many respects I would also be concerned about peformance. The only extra code that runs is a query to see of the procedure exists before running it. I toyed with the idea of attempting the run and parsing the error message I get back (and creating the stored procedure if the error said it did not exist) and I may write an object model for the code generator that does just that, but not today.

You should check out www.hogwarts.tv ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-18 : 16:40:11

db_owner does work for what you have described, so you must have something configured incorrectly.

Could you post your connection string?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-18 : 17:37:47

Try connecting with query analyzer with the user you created and gave dbo rights. Try creating the SP from query analyzer logged in as that user. Does it work there?

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-18 : 17:58:01

Also don't forget that when you re-create the SP you need to have all the permissions on that SP reapplied.

It'd be much better to find out and eliminate the cause of SPs disappearing.

vbwizard
Starting Member

12 Posts

Posted - 2007-10-18 : 18:16:41

The connection string I am using is this: The only changed I have made are to datasource(it was an IP) and to the password (the real password was there). I have tested the user by logging with the user / password credentials using copy/paste right out of the connection string to ensure that no typing errors were made and to double check my user...

workstation id=EDM1TSMITH;packet size=4096;password=************;user id=procedure_admin;data source=sq1;persist security info=False;initial catalog=test

Just to clarify for Van: I am not doing this cause procedures disappear. I am doing this for application continuation once my contract runs out here. They don't have a dba that can fix problems so I want as much self-healing stuff that I can put in here. I am NOT a DBA... I would be the first to say that. I have just enough understanding of SQL to program for it and create sp's and views and design basic databases.....and to be dangerous around a real DBA's servers.

I appreciate all the help that you all are providing here. Thank you very much.
Cheers,
brent

You should check out www.hogwarts.tv ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-18 : 20:18:34

I was hoping to see that the connection string was using Windows authentication which would the problem. But you are correctly using SQL authentication and that's what you setup inside SQL Server for db_owner.

Which database does the procedure_admin account have db_owner in? Double click on the user in SSMS in Logins and let us know exactly what is filled out in user mapping.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

vbwizard
Starting Member

12 Posts

Posted - 2007-10-19 : 09:57:55

In General Tab - Authentication section is set to SQL Server Authentication, In Defaults, the Database is set to Master and the language to English. Server Roles- Nothing is selected as per your previous instructions but I have tried it with System Administrators selected and with Setup Administrators selected (niether worked), In the Database Access Tab I have only one checkmark under the database called test. When you click on the database the database roles box fills out and I have public and db_owner selected. I cannot drop public or I would have done that already, it simply states "Members cannot be dropped from 'public'."

This is SQL Server 2000 so the information your getting is coming from Enterprise Manager. I have tried a similar operation on a completely different computer and version of SQL (sql express on vista laptop) and the public thing is there too, so I don't think thats the problem.

Please excuse my guesses if they are far fetched.

Thanks again for all your help, it is greatly appreciated...

I am guessing here... when the system was originally setup it was setup by a DBA who no longer is here. Is there a way that he could have put in the system that without sa privilges any user created by a lesser privliged user (such as my windows authenticated login) would be denied certain rights? I created this user while logged on as myself authenticated as integrated security. I also know there is some sort of automated script that is running on this server because it backs itself up incrementally every hour and a full backup in the evening after everyone is gone.

You should check out www.hogwarts.tv ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 11:19:57

That's correct that public is not the problem.

I doubt the DBA screwed with you. But to be sure there is no way for him to remove permissions from sysadm fixed server role. Since sysadmin is not working either, I think the issue is outside of SQL Server, perhaps you are logging in with a different account than you think.

I'd suggest running SQL Profiler, making sure to add exceptions to the trace. See who gets logged in when your application receives the exception.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

vbwizard
Starting Member

12 Posts

Posted - 2007-10-19 : 11:54:08

Excellent suggestion. I will do exactly that. Now to find a good book on Profiler. :) I have used it before but not that in-depth and I am sure I can find some docs or help files on it so there is no need to bug you fine people with that. Thanks for your help and once I get the trace data I will let you know the results.

You should check out www.hogwarts.tv ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 12:15:37

One other thing to test...log into Query Analyzer with your procedure_admin account and then attempt to create a stored procedure:

USE Test
GO

CREATE PROC TestProc
AS

SELECT 'Testing db_owner privileges'
GO

Do you get an error when you run this?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

vbwizard
Starting Member

12 Posts

Posted - 2007-10-19 : 17:02:35

Thanks for the additional tip Tara, I have tried this and it does work. I have the results of the SQL trace and even though my connection string explicitly says what user to use, profiler is telling me I am logging in with the user who logged in to the application. This user is a user on sql server as well, and there is another connection string that embeds that users credentials in it. However, I have verified by going line by line that my vb.net sqlconnection object is being built with the string that I posted. I did, however, miss one part of the connection string as it is added during code. I add the command Pooling=False; to the end of the connection string (I have two connection string, the Main user based one and an Admin one. I add the pooling=false to the admin one)

I have verified that the connection string as built has all the appropriate separators (aka I did not forget a semi-colon). I am actually running two procedures.. first I run sp_stored_procedures and then after parsing the return, I run the code to create the sp. In both cases profiler says that I am logging with a different sql server credential than specified in my connection string. Is it pooling my connection from the primary connection string and then reusing it when it finds that the same host is incoming with another request? I do NOT add pooling=False to the primary connection string.

You should check out www.hogwarts.tv ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 17:16:30

So you've got some more debugging to do since the problem has now been identified to be in the application code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

vbwizard
Starting Member

12 Posts

Posted - 2007-10-19 : 17:22:54

Excellent. You have implied that the problem does not lie with the connection string (with the additions that I am making to the connection string) and that is what I needed to know. thanks for your help. I will attempt to trace the issue with the code.
Thanks again for all your help Tara, it is greatly appreciated.

You should check out www.hogwarts.tv ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 17:50:56

Well the problem lies with which connection string that you are using to connect to the database server. The connection string that you have above would work fine inside SQL Server for creating sprocs, but as you've mentioned that's not how you are getting connected. So perhaps you are using a different connection string or are building it on the fly.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources