Annonymous Coward (me@home.com) writes:
I am writing an application which I will deploy to my clients. It is
important for security, support, IP reasons etc, that the users are not
able to access my databse schema (i.e. view/modify/run procs etc).
What do I have to do to ensure that users will not be able to access my
database?
It's a little unclear what you mean here. Someone must be able to access
the database, or else the database will not be very useful. In any case,
you can never prevent a user with admin rights in Windows from stopping
the SQL Server service and attaching the database to another instance.
But obviously when it comes to normal users, you can of course keep them
out by not granting them access to the database. That assumes that
they have no need to access your application.
If your question is how to prevent them from accessing the database from
outside your application, there are a couple of options, but keep in
mind that you can never lock out an admin. And all these options require
that you stick to a certain architecture of your application.
1) Put all logic in stored procedures, so if a user runs a stored procedure
from a query window, nothing evil will happen. That is, the procedure
should perform all security checks needed.
2) Use an application role. This solution requires a middle tier on a
separate machine to be secure. If you do this with a two-tier solution,
you need stored the password in the client tier, and obviously it
can be found, even if you hide it. On a middle tier, you can stored
the password in a place where users do not have access.
3) Use a proxy login. Again, this solution requires a middle tier to
be safe. The middle tier authenticates the users, and then logs into
the application with its own login. The users do not need to have
logins in SQL Server.
Also, I am thinking of installing SSE as a seperate instance with a
unique name - the idea being that it keeps my database away from any
that may exist on the clients machine - and thus provides extra
security. Is this a safer alternative than installing SSE under the
default SQLServer instance name?
I think you should give the person who installs your application the choice
of installing a new instance, or reusing an existing instance. I think
most users would prefer the latter. I would also suggest that this is
a more secure solution for the users, since it reduces their surface
area.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx 
