SQL Server Database Access for My Application only

Question

I have been assigned to develop a application which is to be deployed in more than 1000 locations. I have some security concern over the database which i am using. I want to ensure the accessibility of database to my application only. Database contains some very sensitive information on database (Freezed Bank A/c Numbers in which fund to be transferred) which is to be kept read only until it allowed by remote server(accessed by me only).

My idea to achieve above is as follows,

1. During installation of application, sa password will changed by application and password will be known by application.
2. Windows Authentication will be disabled by the application, No windows user will be able to access sql management studio.

Though my concerns are as follows,

1. Anybody de-attaches database and attaches in different installation, access of database will be gained by user.
2. I have to provide the option take backup of database, user can restore the database in different installation.
3. I guess there will be some methods in that ldf-mdf files can be replaced as users is having full file system windows access.

I will not mind if the user gains the read only access to database, but i will strongly insist no write operation should be allowed on database.

Any suggestions how to implement this.

Example : SQLite for .net has some features like encryption of database, which ensures access of database to application, while have full access to file system, But sqlite is not recommended for large scale applications because SQLite only supports a single writer at a time.

Answer 1

A couple of things concern me here. Setting access levels for the db is the least of your concerns. Where is your network and server admin and how are they controlling access to the server? Are you protected behind a firewall, proper port routing and protection?

When it comes to storing bank account numbers, they are never to be stored in any database unencrypted. Build the functions to encrypt for storing and to decrypt the data for viewing. With today's privacy laws, financial institutions have started encrypting customer names, addresses and telephone numbers as well.

Another concern has already been raised. With my point of privacy above, the data should never be available on more than one system, e.g. 1-2 production server(s). Any remote, QA and Development db's must use scrubbed data...no bank or cc account numbers, no phone number, and no address or name.

I will try to dig up some links for you but there's a lot to cover when dealing with sensitive data like account numbers.

Answer 2

I agree wit Shivan Raptor's point that your overall design is having problem.

Deploying such application is fine, but you need to centralize the SQL database. If such database is required to be deployed on remote location, each computer holding such database needs to be bullet-proofed (ie, nobody, even locally, can have access to it; including admin). SQL server deployment are server centric. They are expected to be centralized. As long as someone have admin ID to the computer, your data is not safe.

1. It is best to develop the application, which you can deploy to 1000 location; but all these application will pull the required data from a centralized SQL server in the data-center.

2. Alternatively, deploy the data in the SQL database in your data-centre and configure for secured Publisher/Subscriber relationship between your 1000 location and your central SQL database --This is expensive and unnecessary unless you have a very specific scenario/environment that we are not aware of.

3. Or use other platforms for this like IBM Lotus Domino (same as #2 above).

It is best to do #1. Just remember, to fully prevent write-access; the server cannot be located at the remote office --example: Someone locally can pull the HardDisk off that computer, plug into another computer, make all the changes and plug it back to your computer.