Advice? HIPAA compliant SQL Server Security with Access Front-end 1

[NorthNone]

Back-end database is in SQL Server, front-end in a user-level secured Access database. I have a HIPAA requirement to provide two-key security. In other words, you must pass not just one password test, but two, to get to this data. We have been using domain login authentication integrated with SQL Server to get a trusted connection to the SQL database. Our second key was the user-level security in the front-end database. My manager brought up the issue of someone installing an ODBC connection to the SQL Server database and viewing the data from there. Of course they would have to know specifics about where that database resided to do this. But once they did, they could get to the data with only the domain login (one key). So we decided to implement a group policy prohibiting them from creating an ODBC connection and for good measure to not permit them to install software (e.g. Enterprise Manager which would speed them on their way). Then the creaky wheels of my brain remembered that you can connect via ADO code in a module of any Access database, so what was to stop someone, once logged into the domain and authenticated, from creating a new database and connecting via ADO code, or a file-based DSN for that matter. Access is already installed so you can't lock them out of that.
Am I thinking this through clearly?
Anyway, I may have opened up a hornet's nest of problems, but HIPAA is HIPAA and we must bow down and obey...
I am grateful for any thoughts anyone might have time to share.

"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[mrdenny]

I'm thinking about application roles here.

After the user logs into the SQL Server have the access front end acticate an application role within the database. Assign the rights only to the application role not to the users NT account. This would give you two passwords that they have to go through.

I would also recommend using only stored procedres to view, change, insert and delete data. That way even if someone is able to figure out the appliation accounts password they can't view the data without using the stored procedures.

If you properly secure the system tables they won't be able to view the source of the procedures to see what the paramaters are.

I would also recommend revoking access to queyr the sysobjects table from the public role. This will prevent users from being able to find out what the objects within the database are.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[NorthNone]

So our two keys would be the following?
Lock one: log into the domain with username/password
Lock two: log into SQL Server


"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[mrdenny]

Lock one would be the domain account.
Lock two would be the password for the application id which is required to access the actual data.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[NorthNone]

Well, the application is Microsoft Access. So if our users only used the application we provided for them to get to the data, that would be a secure second lock. The problem is that you CAN access SQL Server data without using Microsoft Access. So the lock on that door doesn't mean they won't come in another door. What if they installed a copy of Enterprise Manager? What if they installed an ODBC connection and got to the data via Excel? Etc.

"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[SQLSister]

Since their user id won;t have rights to the database installing enterprise manager won't do them any good. That's what he means by having the system set up with an application role. YOu might want to read about security and application roles in BOL to get a better understanding. The idea is that you do not give the people the rights to the data except through the application role and then you strongly limit wjhat the application role can do by making all access through stored procedures. SInce the users won;t have the rights to create stored procedures and can't access the tables directly, then they are limited to going through the Access interface and only doing the things you have specifically allowed them to do.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[NorthNone]

Let's say I put the work into creating the application role in SQL Server. Once they logged into the network (which authenticates them in SQL Server) with enough smarts and having found out the location of the SQL database, could they connect to SQL Server via ADO code from within a newly created Access database? Once connected, could they run the stored procedures for which they have permissions?
I am still quite new to SQL Server (you guessed that already) and apologize if my questions are repetitive, redundant and so forth. I GREATLY appreciate your time


"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[SQLSister]

Yes they can run anything for which they have permissions once they are connected, tha's why it is important to carefully plan out what permissions they should have. The beauty of using the application role is that they can't access those objects just using their own network login which helps give you the two-tier security that you need. ANd by setting the permissions only at the stored Procedure level, you can complately comtrol the thinggs they can do with the database because they cannot directly access the tables. This does mean you can't use dynamic SQl, but it would be a bad idea to use it in this case anyway due to the security issue.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[NorthNone]

I have to guarantee that there are two doors/locks/keys needed to get to the data. It is an internal requirement of my organization for a database with health care data.
From what you say I have only one guaranteed door/lock/key to the data:
-login to the network which authenticates them to SQL Server

I can't guarantee that they will use only my specially designed front-end database to get to the SQL Server data; a determined person could use any Access database with the right ADO code to connect.
Unless I missed something....????
Thanks again for your time.



"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[mrdenny]

In order for them to use there own access database (or any other tool for that matter) they would have to have the password for the application role.

When you create an application role you assign a password to it. That password is required to activate the application role.

The way an application role works is this.

I create a database named DB1.
I create an application role in DB1 named appRole1.
I create a user called User1.
User1 is a member of the Public role only.
I assign user1 to appRole1.
I create a table called dbo.data.
I assign select rights to the application role appRole1.

I log into the SQL Server with the account User1.
I run the command

SELECT * FROM dbo.data

I get back an error that I can't read the table.
I now activate the role using the sp_setapprole and the password that I assigned to the role when I created it.

exec sp_setapprole @rolename='appRole1'
go

Now when I run my select statement again I get back the data from the table.

You would never give your users the password to the application role. You would hard code it within your Access Database then compile the access database using the builtin functions of access, and give the users the compilled access application. This way they can not dig into your code and access the data.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[NorthNone]

You're a winner, Mr Denny I think that would satisfy the two key requirement. I already give them .mde's...
How hard it is to do all this? Especially for a newbie.
Thanks to all who helped me out on this.


"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[mrdenny]

It's not all that hard to setup. Testing is going to be the key part.

It's going to take some time to get everything switched over to using stored procedures instead of standard select statements.

If done correctly the users shouldn't ever know that anything was changed (except that you have to give them a new mde file).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[NorthNone]

Thanks again, Mr Denny

"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com