January 21, 2009 at 5:46 am
Hi,
I've encrypted DB using symmetric key and certificate. I'm going to store this DB under client premises and want to prevent client sysadmin from decrypting data. For security perpose, I'm storing and passing the password (required to decrypt certificate to open symmetric key) from buisness layer(java).
But when I pass query(from buisnes layer) to open symmetric key, a simple trace shows the query and exposes the password to sysadmin.
Is there any way that can prevent a query from exposing in profiler?
Any help will greatly appreciated.
Thanx & regards,
Vicky
January 21, 2009 at 8:24 am
There are certain key words which SQL Server will key in on and block the output, however, the Profiler in SQL Server 2008 is smart enough to recognize whether these key words are being used as the actual statements for execution or just as comments. So at this point I don't think you can block a sysadmin whatsoever. Even if you could, anyone who has debugger access can do the same thing by hooking a debugger up and reading memory.
K. Brian Kelley
@kbriankelley
January 22, 2009 at 5:55 am
that means no way to restrict sysadmin at all? :crying:
January 22, 2009 at 6:15 am
onlyvikas2004 (1/22/2009)
that means no way to restrict sysadmin at all? :crying:
No. That's the definition of sysadmin.
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
January 22, 2009 at 6:31 am
And I drop back to the point that even if you could block the Profiler trace, you could not block someone with local Administrator membership from using a debugger and seeing it in memory in any case.
K. Brian Kelley
@kbriankelley
January 22, 2009 at 6:39 am
In addition, an administrator could put a packet sniffer in place and read all of the network packets coming into the server. Unless you've got those encrypted, that'll reveal the password.
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
January 22, 2009 at 7:11 am
Yup, and I've done just that in troubleshooting a poorly performing 3rd party app which we didn't know the password for. The bottom line is all this points to an important security principle most folks forget:
"Security by obscurity is no security."
Obscuring can help, but it shouldn't be relied upon as the security solution.
K. Brian Kelley
@kbriankelley
January 22, 2009 at 8:29 am
All of the above issues are why I believe any encryption should be done in the business layer, not the database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 22, 2009 at 10:53 am
Jack Corbett (1/22/2009)
All of the above issues are why I believe any encryption should be done in the business layer, not the database.
If I'm a system administrator, I still have the debugger. At some point you've got to trust someone. If you say put it in the business layer, that means you're trusting your developers more than your DBAs. Not that you shouldn't have developers to trust. But chances are you have more developers than DBAs, meaning you've got to be trusting more people overall. And if you don't trust your DBAs with this data, are you going to trust them with ETL processes, warehousing, etc.? Why or why not?
K. Brian Kelley
@kbriankelley
January 22, 2009 at 11:32 am
K. Brian Kelley (1/22/2009)
Jack Corbett (1/22/2009)
All of the above issues are why I believe any encryption should be done in the business layer, not the database.If I'm a system administrator, I still have the debugger. At some point you've got to trust someone. If you say put it in the business layer, that means you're trusting your developers more than your DBAs. Not that you shouldn't have developers to trust. But chances are you have more developers than DBAs, meaning you've got to be trusting more people overall. And if you don't trust your DBAs with this data, are you going to trust them with ETL processes, warehousing, etc.? Why or why not?
I think the encryption tools are better in .NET/Java than in SQL Server and if I encrypt it in the application there is no unencrypted data across the network vs. passing unencrypted data to my SQL Server to do the encryption.
I have not exhaustively studied encryption, bu what I have read and experimented with led me to my this opinion. I'm certainly open to understanding why doing it in the DB would be better. I already know you know more about security than I do.
In this specific case the only way to keep the DBA from seeing this private data in Profiler is to encrypt it before it gets there.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 22, 2009 at 12:00 pm
Jack Corbett (1/22/2009)
K. Brian Kelley (1/22/2009)
Jack Corbett (1/22/2009)
All of the above issues are why I believe any encryption should be done in the business layer, not the database.If I'm a system administrator, I still have the debugger. At some point you've got to trust someone. If you say put it in the business layer, that means you're trusting your developers more than your DBAs. Not that you shouldn't have developers to trust. But chances are you have more developers than DBAs, meaning you've got to be trusting more people overall. And if you don't trust your DBAs with this data, are you going to trust them with ETL processes, warehousing, etc.? Why or why not?
I think the encryption tools are better in .NET/Java than in SQL Server and if I encrypt it in the application there is no unencrypted data across the network vs. passing unencrypted data to my SQL Server to do the encryption.
I have not exhaustively studied encryption, bu what I have read and experimented with led me to my this opinion. I'm certainly open to understanding why doing it in the DB would be better. I already know you know more about security than I do.
In this specific case the only way to keep the DBA from seeing this private data in Profiler is to encrypt it before it gets there.
You could always try to go back to the Pharaoh's style for securing things (have someone build you an impregnable system to bury you in, and then bury them in there as well), but that tends to be rough on your security department....:)
Otherwise - you do have to trust someone at some point..
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 22, 2009 at 12:20 pm
I'd have to go with encryption at a level higher than the DB in most cases, hopefully all the way from the presentation layer back. That way, you're not passing clear-text data across a network that can have packet sniffers, et al, on it.
If you want serious, crazy encryption, encrypt at the front end, with a key that the devs have access to, and encrypt in the database that those devs have access to, but make it so it takes both to get data out of the system once it's in. Of course, that has performance and maintenance issues written all over it, but it would be pretty darn secure!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 12:39 pm
I won't disagree with the encryption tools. It's not exactly easy to get encryption working in SQL Server.
SQL Server supports TLS. Has since the SQL 2000 days. Also, IPSEC can do encryption and IPSEC has been around since Windows 2000.
So the whole "unencrypted across the network" is bogus. Sorry, folks, but we have had solutions to prevent unencrypted data flowing across the data into SQL Server. 🙂
EDIT: IPSEC implementation in a Windows OS has been around since Windows 2000. IPSEC has been around longer than that.
K. Brian Kelley
@kbriankelley
January 22, 2009 at 12:50 pm
K. Brian Kelley (1/22/2009)
I won't disagree with the encryption tools. It's not exactly easy to get encryption working in SQL Server.SQL Server supports TLS. Has since the SQL 2000 days. Also, IPSEC can do encryption and IPSEC has been around since Windows 2000.
So the whole "unencrypted across the network" is bogus. Sorry, folks, but we have had solutions to prevent unencrypted data flowing across the data into SQL Server. 🙂
EDIT: IPSEC implementation in a Windows OS has been around since Windows 2000. IPSEC has been around longer than that.
See this is why I pay attention, so I can learn something new. I probably had read the entry in BOL at one point, but having always worked on in-house databases and servers really never had the need to consider it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 22, 2009 at 12:55 pm
K. Brian Kelley (1/22/2009)
Sorry, folks, but we have had solutions to prevent unencrypted data flowing across the data into SQL Server. 🙂
We have em. The question of who uses them is totally another issue. 😉
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply