How can we protect a database in MS SQL 2005 and 2008
Is there a way to protect a specific database within a Microsoft SQL instance in version 2005 and 2008?
We are trying to do this in 2 ways:
Is there a way to go about this?
We are trying to do this in 2 ways:
1.
An instance of SQL may have 5 databases and we want one of them not to be accessible by the SQL Studio or any other apps unless some sort of password is entered.2.
If a DB is detached and attached to another instance; we want the other instance being access by its SQL Studio or its apps have access only if a password is used.Is there a way to go about this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
arnold:
Bitsqueezer:
Vitor Montalvão:
Can #2 be possible?
What we are looking for is more control access.
Bitsqueezer:
You are correct! Totally forgot we can do that in MSSQL; SQL Server logins or Windows authentication.
Ok, #1 is answered.
As for #2, detach/attach database, what we are looking for if we can incorporate some sort of security to the DB that when detached and attached to another unauthorized sql instance, it won't permit it to be accessible.
Note: re-reading the initial question, we meant: "If a DB is detached and attached to a unauthorized SQL instance or PC; we want that instance or it's PC apps not to be able to access the data"
Ok, #1 is answered.
As for #2, detach/attach database, what we are looking for if we can incorporate some sort of security to the DB that when detached and attached to another unauthorized sql instance, it won't permit it to be accessible.
Note: re-reading the initial question, we meant: "If a DB is detached and attached to a unauthorized SQL instance or PC; we want that instance or it's PC apps not to be able to access the data"
Vitor Montalvão:
Same thing, we are looking for if we can incorporate some sort of security to the DB that when detached and attached to another unauthorized sql instance, it won't permit it to be accessible.
Can #2 be possible?
Detach/attach or backup require the user to also have access rights to the system's storage area.
Ssms can be used to access the system remotely.
No matter how much you want, if a person has access to the DB, the data can be transferred.
You are seemingly enquiring about how to restrickt access to a user who now has legs say unlimited access.
You can have two factor auth into the server/workstation (windows OS), as well as into a web site ...
The SSMS/SQL server does not see to have that option.
Ssms can be used to access the system remotely.
No matter how much you want, if a person has access to the DB, the data can be transferred.
You are seemingly enquiring about how to restrickt access to a user who now has legs say unlimited access.
You can have two factor auth into the server/workstation (windows OS), as well as into a web site ...
The SSMS/SQL server does not see to have that option.
ASKER
So in conclusion, there is no way protect a detached DB from being attached in an unauthorized PC with SQL?
Not based on the mdb/ldb SQL side. Without encryption that a certificate will be required to access/decrypt the data there is nothing that I am aware of that would require anything..
ASKER
So you are saying if we set the encryption provided by SQL Server 2005/2008 on a database then the data is protected (even though its been attached to an unauthorized PC/SQL instance)?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Then we can consider using SQL's encryption for the detach/attach issue.
Last question based on this part, can we encrypt a current database with data or encryption only works at creation time?
Last question based on this part, can we encrypt a current database with data or encryption only works at creation time?
You can encrypt/decrypt everytime you want. Just go to the database properties and you'll find the correct option to do that.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is MS's https://technet.microsoft.com/en-us/library/cc278098(v=sql.100).aspx#_Toc189384676
Note it adds overhead to the db server.
Note it adds overhead to the db server.
ASKER
Vitor Montalvão:
arnold:
Understood. Thanx or confirming we can encrypt current working production DB.
arnold:
Gooud point! We didn't consider this point. Please tell us if we understood correctly by your comment "You need to account for the encryption on the application": that is, if we proceed encrypting our SQL productions DB, we have to also evaluate apps that are currently working with the DB? (we have VBA in Excel, Word & Outlook that access theses DB and also other software packages that their DB is in this SQL instance).
If our assumption is correct, we can review the VBAs since we coded it, but what considerations should we take to software packaged currently in use?
If our assumption is correct, we can review the VBAs since we coded it, but what considerations should we take to software packaged currently in use?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much!
Controlling who has rights/access.
If anyone has rights to detach a database, they likely also have backup rights......
...
Are you looking for
Are you looking for a two factor authentication/authorizati