SQL Login Security

Question

SQL Login Security

Aggie D

SSC-Addicted

Points: 443
More actions
March 3, 2004 at 3:59 pm

#82234

I am hoping to find a person much smarter than me for this one. 🙂 Here is my question. I have an application where the logins used by the users are actually sql accounts. I basically pass their creditials to the connection string. Here is my question. Is there a way to keep the user from using either Enterprise Manager or osql to use their login to come in the back door?
thanks,

Viewing 11 posts - 1 through 10 (of 10 total)

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

btomson SSC Journeyman Points: 96 More actions · Answer 1

Not that I am aware of. As long as a user has the login credentials for a standard account they will be able to log on to the box using Enterprise Manager or osql/isql. Is there a reason why your users even have access to Enterprise Manager/osql/isql?

The only other thing I can think of is to assign the user account linked to the login account the appropriate database roles (datareader and datawriter) so that they can only access the data instead of being able to alter table and index structure and what not. Don't know if that helps or not.

-Brett

Kevin-127902 SSC Rookie Points: 45 More actions · Answer 2

Kevin-127902

SSC Rookie

Points: 45

March 3, 2004 at 11:48 pm

#497155

You should explore application roles. See BOL

Hendra Eka Putra SSCrazy Points: 2015 More actions · Answer 3

You should only use an application role whenever the permission of user are not totally different one to another. Unless you want to configure user login as much as your user

tverweij SSC-Addicted Points: 429 More actions · Answer 4

Don't know if I am smarter 🙂

For security issues, we kept our SQL Server outside the domain and placed behind a firewall, so no one can login on windows credentials or from outside the building.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 5

If your users are connecting through an application server you can put the SQL Server behind a firewall and just allow access through the firewall to the app server IP address, thus denying enyone else access.

Shamless self promotion - read my blog http://sirsql.net

Aggie D SSC-Addicted Points: 443 More actions · Answer 6

I would like to thank everyone for their advise. To answer the first question. We have custom Roles that have execute privaledges to the stored procedures. The user accounts are placed in the appropriate roles to give them exec perms.

In all truth, I am not sure what way we are goign to go.

Thanks for the help.

Aggie D SSC-Addicted Points: 443 More actions · Answer 7

I would like to thank everyone for their advise. To answer the first question. We have custom Roles that have execute privaledges to the stored procedures. The user accounts are placed in the appropriate roles to give them exec perms.

In all truth, I am not sure what way we are goign to go.

Thanks for the help.

Curtis Smith Hall of Fame Points: 3063 More actions · Answer 8

Here is what I did for one of my applications: I created a READONLY, database with one table that holds a username and password, which is encrypted, using my own routine. The application first connects to that database and retrieves that username and password, decrypts it and uses that information to connect to the "real" database. For all purposes, there is really only two users on the server, the "APP_READER" and the "APP_WRITER". The "APP_READER" has access to only the READONLY DB and the writer has access to the "real" database. Using this method, noone really knows what the username/password is for the server, they cannot connect. One drawback is that since everyone connects as the same user, it could be difficult to determine who has what connection. There are several ways around this, I'll list only two: 1) append the app username to the DBParm string 2) have the application log the SPID and app username in a table. SP_WHO(and SPWHO2) will give you most of the information about the connections.

-- Hope this helped

Oh, and by the way, according to my wife I'm not smarter than anybody!

Curtis Smith
SQL Server DBA
Well in worked in Theory ...

mise13 SSC Enthusiast Points: 128 More actions · Answer 9

I think application roles will give you what you need.

From BOL:

When an application user connects to an instance of SQL Server using Windows Authentication Mode, an application role can be used to set the permissions the Windows NT 4.0 or Windows 2000 user has in a database when using the application. This method allows Windows NT 4.0 or Windows 2000 auditing of the user account and control over user permissions, while she uses the application, to be easily maintained.

All the rights to the tables are given to the application role, not the actual user.

puredesi5 Valued Member Points: 50 More actions · Answer 10

"I am hoping to find a person much smarter than me for this one. "

Is it possible to find smarter person than you? I don't think so!!!