Secure access to a SQL Server database

From: Larry Charlton (LarryCharlton_at_discussions.microsoft.com)
Date: 06/30/05 

Date: Wed, 29 Jun 2005 18:22:02 -0700

I've been scouring knowledgebase and the web for information or examples of
securely accessing SQL Server. I'm wondering if there are any guidelines
with implementation examples for using SQL Server in a secure and well
performing fashion. The context that I am approaching this from is:

I have people developing VB Scripts, ASP, ASP.NET (vb, c#), and VB6
applications. Most users of these scripts/apps are in our domain (with a
smattering outside). In addition they may use a series of products to
extract data if appropriate outside of the applications. Vendor applications
brought in seem to lack security or as an afterthought and I've yet to see
any "that's great!" implementations that seem they would be reusable.

VB Scripts I think we have mostly fixed for both SQL Server users and NT
Authentication. Please comment if you see things we missed. Basically we
have a com object that will generate a 10 to 20 byte hash value based
optionally on some combination of the following: script launch location,
script text, computer launched from, user launching. These get registered
with the server and a user with permissions moves it to a validation table.
When the script is normally running every call to a stored procedure includes
the hash value that gets authenticated against the secure table. The
connections are encrypted leaving the hash open mostly to memory probes?

ASP Currently we use SQL Server authentication and stuff something in a
registry semi encrypted with some level of security on the keys and pray no
one looks at it. I'd really love to use NT Authentication for a surogate
user if there's a secure way to do it.

ASP, ASP.NET I suppose we could use encrypted credentials and build a
mechanism to communicate password changes from the servers? But how do I
authenticate whether I should send a new encrypted password (securely)?
ASP.NET I suppose I could just visit the machine(s) and use a utility to
update the encrypted passwords. BTW are there links on how and why the data
protection api is secure? I'm not a big fan of security by obscurity.

VB6 I'm totally lost although I suppose we could look at using a variant of
the VB Script protection.

Ideally I'd like to be able to run dynamic sql (don't ask :)) including
using tables, views, and stored procedures, and be fairly comfortable that no
one was using them (or more specifically updating data) ouside the contexts
I've allowed (i.e. applications, background processes, etc) even if they are
launched from non secure computers (aren't they all?). Ideally I'd also be
able to apply NT authentication for data access from reporting, analysis, etc
to the same users that use the applications to update data (while preventing
use of Access, Excel, etc, etc).

Thanks for any input and sorry it's so long.

Larry

Relevant Pages

  • Re: Sybase to Access
    ... Windows NT authentication is _WONDERFUL_ in SQL Server. ... but altogether it is pretty powerful; pretty secure. ... Master Programmer wrote: ...
    (microsoft.public.dotnet.languages.vb)
  • Re: SQL Server over the Web, what are my options?
    ... get computer authentication and encryption of the traffic. ... PPTP can be just as cryptographically secure as IPsec if you use 128-bit ... >> 2) On the firewall at B, forwarding traffic from A to SQL server. ...
    (microsoft.public.security)
  • Re: which is more secure?
    ... SQL Server can be as secure as AD ... Another option to consider is to use ADAM as your user store or a hybrid ... do pass through authentication to AD, so ADAM users would be authenticated ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Login Page
    ... Not sure what this has to do with SQL Server? ... You can mark a site, virtual directory, or folder as secured by Windows ... Authentication by turning off anonymous access. ... > I am looking for a way to make my website secure. ...
    (microsoft.public.sqlserver.server)
  • Where does SQL Server store authentication type?
    ... We can have 'SQL Server and Windows' or 'Windows only' authentication. ... I'd like to write script and check this setting automatically. ...
    (microsoft.public.sqlserver)