By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

Authentication between windows application and SQL server

P: n/a
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
Jul 4 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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" <sp**@spam.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
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

Jul 4 '06 #2

P: n/a
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" <dave@jwa[remove.this]online.comwrote in message
news:ew**************@TK2MSFTNGP03.phx.gbl...
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" <sp**@spam.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
>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


Jul 5 '06 #3

P: n/a
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/de...natetopic1.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

"Gav" <sp**@spam.comwrote in message
news:eP****************@TK2MSFTNGP03.phx.gbl...
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" <dave@jwa[remove.this]online.comwrote in message
news:ew**************@TK2MSFTNGP03.phx.gbl...
>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" <sp**@spam.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
>>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



Jul 5 '06 #4

P: n/a
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.
"Gav" <sp**@spam.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
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

Jul 5 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.