How to prevent our database to access any person

Question

How to prevent our database to access any person

dsachu

SSCrazy

Points: 2242
More actions
September 19, 2010 at 1:02 am

#75576

Hi everyone,
I have an issue with my database securities. Please help me regarding below comments,
I have a database, in which lot of tables, procedures, views & functions. All the thing in my system like sql 2005 and i am admin for the own system. I have every rights for my self PC.
Suppose i am giving my database to other person after taking backup (.bak), who using own system. person is admin for own system (he is using another laptop & also sql 2005 there) .
The problem is coming when i give own database to other person, he can see my database with SSMS environment. he can view my all database tables, procedures, views & functions after restoration of data in own system(Laptop).
Can any body help to set any password on own database by which other person(who has sysadmin right and he can login with sa) can't access my database whenever he restore my database to own system.
How i can prevent those person to doesn't open my database.
Please help me.
🙂
Thanx & Regards,
Sachin.

Viewing 14 posts - 1 through 13 (of 13 total)

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

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

You can't. There are things you can do to slow them down, but if a person is a SQL administrator, they have complete and total control over the server and you can't keep them out of the DB if they want to look.

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

dsachu SSCrazy Points: 2242 More actions · Answer 2

Hi Gail,

I have .net application as well as database for the same.

For application, we are converting it as dll and then send to our client.

After converting in dll, client not able to see code behind the application, while he will use applicaton.

Just like same, i aslo want same activity in my database,

Is any way by which i can convert or prevent database tables, procedures, funcations, views from client.

I don't want client see my database in own system where SSMS is available as well as client itselft System administrator.

If Answer is no, then i think microsoft should be work for the same. Like password security on database. client only restore the database for the application. By which application can use the database path through the web Config only.

Eeve client should not be rights to run select command on the particular database.

I want only this database is used by application not client.

Because i am only giving database for application, not for sharing information like what & how

i have done in database.

🙂

Thanx & Regards,

Sachin.

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

There is no way you can prevent someone who is a SQL sysadmin from doing what they want with the database. A sysadmin by definition has full permissions to everything on the server.

If you don't want people seeing the DB, you should be hosting it, not giving it to the client.

If it's that you don't want your code copied and changed, put a licence agreement with penalties for violation, like just about every ISV that uses SQL databases does.

You can encrypt views and procedures, but it's easily reversible and at best will slow someone down.

p.s. If you think that your .net code is safe because you gave someone a dll, think again. Unless you used some form of obfuscater, it is trivial to decompile a .net dll and get back the source code. There are free tools that do it. Eg http://www.red-gate.com/products/reflector/

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

Lowell SSC Guru Points: 323504 More actions · Answer 4

I know the feeling...you put a lot of hard work and time into designing the database objects, and you don't want anyone to look at them because they could infer how to do it themselves, and steal/adapt your ideas;

As Gail said, all you can do is slow them down from looking at your code or data; you cna use the WITH ENCRYPTION command when building your procs/views/functions, but the sysadmin on the box can decrypt them with a tiny bit or research or knowledge.

even your .NET DLL's can be reverse engineered and the code can be read...each project,class, function and proc ....even comments.

scary, huh?

A lot of it comes down to trust and professionalism. if you don't trust someone, you'll want to host the database yourself. most professional DBA's and developers don't care about your code, as long as it runs efficiently.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

Lowell (9/19/2010)
I know the feeling...you put a lot of hard work and time into designing the database objects, and you don't want anyone to look at them because they could infer how to do it themselves, and steal/adapt your ideas;

Personal opinion: Worrying about that is silly.

If someone wanted to write an app, they would. If they're buying yours it's because they don't have time/skills to do it themselves.

Take for example Microsoft sharepoint. Written in .net and SQL, all the source code is accessible. Does that mean I'm likely to spend hours pouring over the code to write a sharepoint clone for myself instead of using Sharepoint itself? I could, but it would be utterly stupid to do so.

It's highly unlikely that there's anything so revolutionary in your code/db design that it's worth someone reverse engineering and taking. If you feel differently, host the databases yourself.

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

dsachu SSCrazy Points: 2242 More actions · Answer 6

I hope microsoft will provide, any facility for the same in future.

Sometime such cases come for DBA or developer. He don't want to share own database

Sachin

Lowell SSC Guru Points: 323504 More actions · Answer 7

kumar.sachu08 (9/19/2010)
I hope microsoft will provide, any facility for the same in future.
Sometime such cases come for DBA or developer. He don't want to share own database
Sachin

the technology already exists. you can load/unload an xml file to and from your datasets (dataset.ReadXML and dataset.WriteXml methods), and encrypt/decrypt the xml file so no one can look at it.

Remember the objective of an agency having SQL Server is they have control over their own data. if you don't agree with that premise...that the clients data is theirs even though you built the framework the data will reside in, then you'll need to shift to less popular but more secure formats.

you just cannot use SQL Server and hide everything from the administrator of that system.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

kumar.sachu08 (9/19/2010)
Sometime such cases come for DBA or developer. He don't want to share own database

In those cases the DBA or developer should host the database themselves. If you give a database to someone else, you are giving them full control over it.

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

dsachu SSCrazy Points: 2242 More actions · Answer 9

GilaMonster (9/19/2010)
kumar.sachu08 (9/19/2010)
Sometime such cases come for DBA or developer. He don't want to share own database
In those cases the DBA or developer should host the database themselves. If you give a database to someone else, you are giving them full control over it.

Other Person (Client) not give me permission to access own server for the security point of view. he is self sql administrator. So, we can't do any such activity. we only give .bak file nothing more.

dsachu SSCrazy Points: 2242 More actions · Answer 10

Lowell (9/19/2010)

the technology already exists. you can load/unload an xml file to and from your datasets (dataset.ReadXML and dataset.WriteXml methods), and encrypt/decrypt the xml file so no one can look at it.

Can u please make sure which tecnology u are talking .Net or SQL Server, if SQL Server , I don't have any idea about this syntax . Can u go through step by step by which i can learn somethin new concept from you. This will totally new for me and I will obliged to you for your gracious hospitality.

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

kumar.sachu08 (9/19/2010)
Other Person (Client) not give me permission to access own server for the security point of view.

No, I don't imagine he would.

he is self sql administrator. So, we can't do any such activity. we only give .bak file nothing more.

What I'm saying is if you want to completely control the SQL database, you must host the SQL server, put the SQL database on there and only let the client access it, not administer it.

Your server.

Your database.

If you can't do that, and are giving someone else the backup file, you are giving them complete and total control over that database.

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

dsachu SSCrazy Points: 2242 More actions · Answer 12

GilaMonster (9/19/2010)

If you can't do that, and are giving someone else the backup file, you are giving them complete and total control over that database.[/quote-0]

That's why i am saying microsoft should be work for the same. Provide password facility on backup file, by which no one can execute any command when i set password. Only he can restore database in own SSMS.

Regards,

Sachin:-)

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

kumar.sachu08 (9/19/2010)
That's why i am saying microsoft should be work for the same. Provide password facility on backup file, by which no one can execute any command when i set password. Only he can restore database in own SSMS.

If that was possible, how would the administrator be able to do maintenance (backups, index rebuilds, stats updates)?

Even if that were possible, an admin could still extract code from the database caches, use SQL Trace to see what's been run by the application and reset your application's password (or maybe even see what it is, depending how you create it) and gain access that way.

If you think it's such an essential feature, go tell Microsoft: http://connect.microsoft.com/

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