<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 7,2000
»
Security
»
What is the best way to Secure Production...
What is the best way to Secure Production Data from Developers SQL2000
Rate Topic
Display Mode
Topic Options
Author
Message
Osmar Fernandez
Osmar Fernandez
Posted Friday, December 19, 2008 8:01 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, March 25, 2010 12:13 PM
Points: 6,
Visits: 16
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.
Post #622982
GilaMonster
GilaMonster
Posted Friday, December 19, 2008 8:18 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 37,683,
Visits: 29,938
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 2008, MVP
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
Post #623008
Osmar Fernandez
Osmar Fernandez
Posted Friday, December 19, 2008 12:30 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, March 25, 2010 12:13 PM
Points: 6,
Visits: 16
How else can it be done w/o using Windows Authentication?
Post #623273
Osmar Fernandez
Osmar Fernandez
Posted Friday, December 19, 2008 1:25 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, March 25, 2010 12:13 PM
Points: 6,
Visits: 16
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?
Post #623322
GilaMonster
GilaMonster
Posted Friday, December 19, 2008 1:47 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 37,683,
Visits: 29,938
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 2008, MVP
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
Post #623337
Osmar Fernandez
Osmar Fernandez
Posted Friday, December 19, 2008 3:15 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, March 25, 2010 12:13 PM
Points: 6,
Visits: 16
Still, If I can Access the Store Procedure from Another App (ex: Access) I can push incorrect data or modify data with no integrity.
Post #623372
GilaMonster
GilaMonster
Posted Saturday, December 20, 2008 12:42 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 37,683,
Visits: 29,938
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 2008, MVP
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
Post #623436
Osmar Fernandez
Osmar Fernandez
Posted Saturday, December 20, 2008 10:02 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, March 25, 2010 12:13 PM
Points: 6,
Visits: 16
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?
Post #623486
GilaMonster
GilaMonster
Posted Saturday, December 20, 2008 10:55 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 37,683,
Visits: 29,938
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 2008, MVP
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
Post #623491
RBarryYoung
RBarryYoung
Posted Saturday, December 20, 2008 10:16 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855,
Visits: 9,374
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.
-- RBarryYoung
,
(302)375-0451
blog:
MovingSQL.com
, Twitter:
@RBarryYoung
Proactive
Performance Solutions, Inc.
"Performance is our middle name."
Post #623541
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
