Authentication between windows application and SQL server

[Gav]

Hi,

I am writing a windows form application (C#) which access's data from an SQL
server. The SQL server is using windows authentication only.

At the moment I have to grant the domain users access to the database for
the application to work. However, the database includes information that I
need to hide from the users, currently the application is taking care of
that. There is nothing stopping the users from accessing the database in a
different way (I.e. ODBC via excel/access project).

Obviously this isn't very secure... anybody come across this and found a
more secure way of doing this?

I was thinking along the line of the possibility of getting a windows
application to authenticate a particular domain user like you can in a web
application? then maybe I can encrypt the password and secure the data a
little better.

Any help would be great

Thanks
Gav

 

[Dave Sexton]

Hi Gav,

1. Lock down the Sql Server by using the built-in security model. For
instance, grant users that require read-only access the dbreader permission
and nothing else for a particular View. Grant execute permission to users
for Stored Procedures that they may execute. Do not grant any permissions
for the master database. To make management easier for handling security
changes in the future you could create custom roles in Sql Server or groups
in Windows such as OperationsManagers or ReportAnalysts and grant these
roles access to certain db resources instead of each individual user.

2. You could create a single Windows account for your application and your
application could impersonate that account when executed. This would
require some work to ensure that the credentials are secured and you'd lose
accountability since every user would use the same account for data access.

HTH

 

[Gav]

Hi Dave,

Thanks for the reply, I would like to go down the route of suggestion 2.
However I'm not sure how to get my windows application to impersonate
another user. Does anybody know of any websites they could point me to on
this subject?

Regards
Gav

 

[Dave Sexton]

Hi Gav,

To impersonate a Windows account use the
System.Security.Principal.WindowsIdentity and the System.Threading.Thread
class. MSDN article provides examples in multiple languages:

http://msdn.microsoft.com/library/d...ipalwindowsidentityclassimpersonatetopic1.asp

The examples need to be updated but they should work.

To set the current Thread's principal in C#:

System.Threading.Thread.CurrentPrincipal = new
WindowsPrincipal(impersonatedWindowsIdentity);

HTH

 

[Norman Yuan]

The simplest way is to create different user groups on your Windows domain
network, and add users (accounts) into different user group. Then on the SQL
Server, you do not create individual login mapping to each windows domain
user account, rather, you create SQL Server login that maps to a Windows
domain user group. Say, you have to SQL Server logins that maps to tow user
group: Domain\GeneralUsers and Domain\AdvancedUsers. So, you can give
different access permission to the two Windows domain user groups/SQL Server
logins.

The other way to secure SQL Server database you use in conjunction to
Windows Authentication is to only give SQL Server database user permission
to SPs, nothing else. This way, no matter what user tries to connect to SQL
Server with (ODBC, EXCEL...), he/she can only see the SPs that he/she has
permission to. This is commonly recommended approach.