MSSQL 2005 - limit db access to my app only

[TWSupport]

Hi

I'm new to all this stuff so I was just hoping someone could point me into the right direction.

I am writing an app which will hold some sensitive information including names, addresses, peoples salary etc. The users will connect to the database via an ODBC connection on their local computer.

What I need to know is there anyway I can make SQL server only accept connections (for that particular database) that come from the application I am writing?

TIA,

Arms

 

[jrbarnett]

You can set the SQL security up so that only accounts for this application have access to the database; you can also use the built in encryption functionality in SQL 2005 to secure the personal data.

As a suggestion, you could possibly use a DSN less connection to connect to your server from your application, which means there is less of an easy way for somebody else to try and get into your app via other means.
For example:

Provider=SQLOLEDB;Data Source=server;Initial Catalog=database name;User ID=username;Pwd=password;Network Library=DBMSSOCN;APP=Your Application Name

Network Library=dbmssocn means "Connect over TCP/IP" but if it is running on the same host as your client, then you could change this to connect via shared memory.
See

http://www.connectionstrings.com/

to learn more about writing database connection strings.

John

 

[SQLSister]

Also, write all of your code that accesses the data in stored procs (and do not ever use dynamic SQL). Then do not give rights to the tables and views to anyone except the admin. Make all the user access rights at the stored proc level and then they can only do what the stored proc does.

"NOTHING is more important in a database than integrity." ESquared

 

[TWSupport]

thanks very much guys, many hours of fun to look forward to