What is the best way to Secure Production Data from Developers SQL2000

Question

What is the best way to Secure Production Data from Developers SQL2000

Osmar Fernandez

SSC Journeyman

Points: 98
More actions
December 19, 2008 at 8:01 am

#94293

I have a Team of 6 Developers. We write code in VB.NET and ASP.NET with SQL 2000.
We connect to SQL using SQLConnection() and Building a String to Connect with User and Password.
What Are The Best Possible Solucions to Secure Access to The Production Data from the Developers.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

A hardcoded password is a bad idea, for this reason and others.

You can put the password in the app.config file (encrypted) and have it different on dev and prod. Preferably use windows authentication, then you can add all the users of the app to a windows group, grant that group login rights on SQL and just the required rights on prod.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Osmar Fernandez SSC Journeyman Points: 98 More actions · Answer 2

How else can it be done w/o using Windows Authentication?

Osmar Fernandez SSC Journeyman Points: 98 More actions · Answer 3

BTW, there is a problem with windows authentication. If the user has access he can use Excel and update the information from it and that is why I do not use it.

Can you control the access to SQL using windows authentication and Application role and something else?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Osmar Fernandez (12/19/2008)
BTW, there is a problem with windows authentication. If the user has access he can use Excel and update the information from it and that is why I do not use it.

Not if all access is through stored procedures and the windows accounts have no rights on the base tables. Which is the recommended practice.

You can do it with windows authent and app roles. It gets complex and there are downsides to app roles. If the app is written with all queries direct to the tables (why?) then you may have to go with an encrypted password/connection string in the app.config file. Make sure it's different for dev and prod. I don't know the technical details of that (not a C#/VB developer) but it is how things were at my previous company.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Osmar Fernandez SSC Journeyman Points: 98 More actions · Answer 5

Still, If I can Access the Store Procedure from Another App (ex: Access) I can push incorrect data or modify data with no integrity.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Osmar Fernandez (12/19/2008)
Still, If I can Access the Store Procedure from Another App (ex: Access) I can push incorrect data or modify data with no integrity.

Which is also the case with SQL login and password when the password becomes known (and with a single hardcoded password, that will happen). Aren't the procs the things enforcing the integrity?

You can use approles, just be aware of the downside (no connection pooling). You can use an encrypted password. You can create a proc that checks the calling app name to be sure that it's your app and not something else, and have every proc call that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Osmar Fernandez SSC Journeyman Points: 98 More actions · Answer 7

Let's Say that I use an Encrypted Pwd.

1. Does SQL have a way to internally Decrypt The Pwd?

2. If I have to Write a Class to Decrypt it, them I'm Back in the same Situation With the Developer. They can Use this class and Get To the Pwd.

3. What are the options to handle Encrypted Pwds?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Osmar Fernandez (12/20/2008)
Let's Say that I use an Encrypted Pwd.
1. Does SQL have a way to internally Decrypt The Pwd?

No. The encryption I'm talking about is purely within the client app. SQL's passwords have to be passed to it plain text. That's why SQL logins are considered less secure than windows authentication, where passwords are not passed around, only tokens.

2. If I have to Write a Class to Decrypt it, them I'm Back in the same Situation With the Developer. They can Use this class and Get To the Pwd.

As I said, not my area of expertise. This is more a VB/C# problem. The big problem is managing/hiding the encryption keys. The class isn't the issue, the key is.

Perhaps ask about this on a good .net forum?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Osmar Fernandez (12/19/2008)
Still, If I can Access the Store Procedure from Another App (ex: Access) I can push incorrect data or modify data with no integrity.

Not if you write the stored procedures to validate the integrity of the data that they receive.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]