| Groups | Blog | Home |
sql server new users : SQL Server Security
I am in the process of writing some SQL based reports. The will be around 3 groups of reports. The setup will be Group 1 will be viewable by all staff. Group 2 will be viewable by managers, and above. Some accounts staff may also require access. Group 3 will be only viewable by the board of directors More than likely, the reports will be distributed as 3 separate applications. These reports will be used in my company only. I don't know much about SQL security, so I'm looking for some advice. I see two ways of doing this: 1) Set up 3 users in SQL Server - Group1User, Group2User, Group3User. When the application launches, have the user enter a password for the application. The App then logs into SQL Server, using the GroupXUser logon. 2) Set up each user in SQL Server. While I can use a Windows group for the Group 1 reports, I will need to specify individual users for the Group 2 reports. So I'd like some advice about which method I should choose. Thanks Vayse
You would probably want three different sql user accounts as it is easy to
detect the login on the application and use a different view to present the data. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Vayse" <vayse@deadspam.com> wrote in message news:uCHgT336GHA.4620@TK2MSFTNGP02.phx.gbl... > Hi > I am in the process of writing some SQL based reports. > The will be around 3 groups of reports. The setup will be > Group 1 will be viewable by all staff. > Group 2 will be viewable by managers, and above. Some accounts staff > may also require access. > Group 3 will be only viewable by the board of directors > More than likely, the reports will be distributed as 3 separate > applications. > > These reports will be used in my company only. I don't know much about SQL > security, so I'm looking for some advice. > I see two ways of doing this: > 1) Set up 3 users in SQL Server - Group1User, Group2User, Group3User. When > the application launches, have the user enter a password for the > application. > The App then logs into SQL Server, using the GroupXUser logon. > > 2) Set up each user in SQL Server. While I can use a Windows group for the > Group 1 reports, I will need to specify individual users for the Group 2 > reports. > > So I'd like some advice about which method I should choose. > > Thanks > Vayse > >
Thanks.
When I was checking the help on enabling mixed mode login, I noticed that Microsoft recommends against it. It doesn't give a reason. Have you any idea why this is? [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:ebIqUb46GHA.2248@TK2MSFTNGP04.phx.gbl... > You would probably want three different sql user accounts as it is easy to > detect the login on the application and use a different view to present > the data. > > -- > Hilary Cotter > Director of Text Mining and Database Strategy > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. > > This posting is my own and doesn't necessarily represent RelevantNoise's > positions, strategies or opinions. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > > > > "Vayse" <vayse@deadspam.com> wrote in message > news:uCHgT336GHA.4620@TK2MSFTNGP02.phx.gbl... >> Hi >> I am in the process of writing some SQL based reports. >> The will be around 3 groups of reports. The setup will be >> Group 1 will be viewable by all staff. >> Group 2 will be viewable by managers, and above. Some accounts staff >> may also require access. >> Group 3 will be only viewable by the board of directors >> More than likely, the reports will be distributed as 3 separate >> applications. >> >> These reports will be used in my company only. I don't know much about >> SQL security, so I'm looking for some advice. >> I see two ways of doing this: >> 1) Set up 3 users in SQL Server - Group1User, Group2User, Group3User. >> When the application launches, have the user enter a password for the >> application. >> The App then logs into SQL Server, using the GroupXUser logon. >> >> 2) Set up each user in SQL Server. While I can use a Windows group for >> the Group 1 reports, I will need to specify individual users for the >> Group 2 reports. >> >> So I'd like some advice about which method I should choose. >> >> Thanks >> Vayse >> >> > >
It was highly discouraged in SQL Server 2000 since it is not very secure.
However, with SQL Server 2005, SQL Server security is much improved, and may serve your needs if you don't have all SQL Server users in the AD. -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous [quoted text, click to view] "Vayse" <vayse@deadspam.com> wrote in message news:OZ3TCVI7GHA.3620@TK2MSFTNGP04.phx.gbl... > Thanks. > When I was checking the help on enabling mixed mode login, I noticed that > Microsoft recommends against it. It doesn't give a reason. > Have you any idea why this is? > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:ebIqUb46GHA.2248@TK2MSFTNGP04.phx.gbl... >> You would probably want three different sql user accounts as it is easy >> to detect the login on the application and use a different view to >> present the data. >> >> -- >> Hilary Cotter >> Director of Text Mining and Database Strategy >> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. >> >> This posting is my own and doesn't necessarily represent RelevantNoise's >> positions, strategies or opinions. >> >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> >> >> >> "Vayse" <vayse@deadspam.com> wrote in message >> news:uCHgT336GHA.4620@TK2MSFTNGP02.phx.gbl... >>> Hi >>> I am in the process of writing some SQL based reports. >>> The will be around 3 groups of reports. The setup will be >>> Group 1 will be viewable by all staff. >>> Group 2 will be viewable by managers, and above. Some accounts staff >>> may also require access. >>> Group 3 will be only viewable by the board of directors >>> More than likely, the reports will be distributed as 3 separate >>> applications. >>> >>> These reports will be used in my company only. I don't know much about >>> SQL security, so I'm looking for some advice. >>> I see two ways of doing this: >>> 1) Set up 3 users in SQL Server - Group1User, Group2User, Group3User. >>> When the application launches, have the user enter a password for the >>> application. >>> The App then logs into SQL Server, using the GroupXUser logon. >>> >>> 2) Set up each user in SQL Server. While I can use a Windows group for >>> the Group 1 reports, I will need to specify individual users for the >>> Group 2 reports. >>> >>> So I'd like some advice about which method I should choose. >>> >>> Thanks >>> Vayse >>> >>> >> >> > >
It's still highly discouraged in SQL Server 2005.
In terms of some of the specific reasons why it's less secure, those are numerous. If you use SQL logins, it means you start having passwords floating around all over the place. Use a sql login for a command line routine and you pass in a password. Use it for an app connection, the app has a password out there somewhere, etc. In terms of auditing, you don't really know who SQL Login User is. It's much easier to track windows logins and people aren't as likely to give out there network login name and password as they would be with SQL login in password. You can login using sa when SQL authentication is enabled. A lot of the hacks against SQL Server involved cracking tools to break the sa password. I've seen corporate environments where people who had no business having sa passwords managed to get them, use them and cause production server downtime from doing things they didn't know enough about to be doing. SQL passwords can go across the network in clear text - if you log in using Windows Authentication, no password is sent across the network. The list goes on - that's just a short list. SQL Server 2005 has implemented some new security features to mitigate the risks but it doesn't necessarily mean that people will learn these features and also implement them. In some cases - such as Password policies for SQL logins - they may not be able to as this only works on Windows Server 2003. -Sue On Wed, 11 Oct 2006 00:33:54 -0700, "Arnie Rowland" [quoted text, click to view] <arnie@1568.com> wrote:
>It was highly discouraged in SQL Server 2000 since it is not very secure. > >However, with SQL Server 2005, SQL Server security is much improved, and may >serve your needs if you don't have all SQL Server users in the AD.
Agreed. I don't use or recommend SQL Logins for many, many reasons.
In the message above, note the use of 'improved' and 'may'. -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous [quoted text, click to view] "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message news:tm9qi2dfetimn8gahvf83i4u8u75m6u7of@4ax.com... > It's still highly discouraged in SQL Server 2005. > In terms of some of the specific reasons why it's less > secure, those are numerous. > If you use SQL logins, it means you start having passwords > floating around all over the place. Use a sql login for a > command line routine and you pass in a password. Use it for > an app connection, the app has a password out there > somewhere, etc. > In terms of auditing, you don't really know who SQL Login > User is. It's much easier to track windows logins and people > aren't as likely to give out there network login name and > password as they would be with SQL login in password. > You can login using sa when SQL authentication is enabled. A > lot of the hacks against SQL Server involved cracking tools > to break the sa password. I've seen corporate environments > where people who had no business having sa passwords managed > to get them, use them and cause production server downtime > from doing things they didn't know enough about to be doing. > SQL passwords can go across the network in clear text - if > you log in using Windows Authentication, no password is sent > across the network. > The list goes on - that's just a short list. > > SQL Server 2005 has implemented some new security features > to mitigate the risks but it doesn't necessarily mean that > people will learn these features and also implement them. In > some cases - such as Password policies for SQL logins - they > may not be able to as this only works on Windows Server > 2003. > > -Sue > > On Wed, 11 Oct 2006 00:33:54 -0700, "Arnie Rowland" > <arnie@1568.com> wrote: > >>It was highly discouraged in SQL Server 2000 since it is not very secure. >> >>However, with SQL Server 2005, SQL Server security is much improved, and >>may >>serve your needs if you don't have all SQL Server users in the AD. >
Okay but the question was:
[quoted text, click to view] >When I was checking the help on enabling mixed mode login, I noticed that I didn't really see an answer to the posters question -> Microsoft recommends against it. It doesn't give a reason. > Have you any idea why this is? other than "2000 it was not very secure." Mentioning that it has improved and may meet your needs doesn't really address the risks that still exist, what the issues are (which is what the poster asked) and doesn't really leave one with the impression that it is still recommended to use windows authentication. I wanted to make sure the poster wasn't making any assumptions otherwise. I would have made some bad assumptions if I didn't know a little about SQL Server security. -Sue On Wed, 11 Oct 2006 11:41:20 -0700, "Arnie Rowland" [quoted text, click to view] <arnie@1568.com> wrote:
>Agreed. I don't use or recommend SQL Logins for many, many reasons. > >In the message above, note the use of 'improved' and 'may'.
Thanks Sue and Arnie for the detailed answers.
I reckon I will create a windows groups for each group of reports. Thanks Vayse [quoted text, click to view] "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message news:6kfqi29ndl987jm90id91dpht1uajmde39@4ax.com... > Okay but the question was: >>When I was checking the help on enabling mixed mode login, I noticed that >> Microsoft recommends against it. It doesn't give a reason. >> Have you any idea why this is? > I didn't really see an answer to the posters question - > other than "2000 it was not very secure." > Mentioning that it has improved and may meet your needs > doesn't really address the risks that still exist, what the > issues are (which is what the poster asked) and doesn't > really leave one with the impression that it is still > recommended to use windows authentication. I wanted to make > sure the poster wasn't making any assumptions otherwise. > I would have made some bad assumptions if I didn't know a > little about SQL Server security. > > -Sue > > On Wed, 11 Oct 2006 11:41:20 -0700, "Arnie Rowland" > <arnie@1568.com> wrote: > >>Agreed. I don't use or recommend SQL Logins for many, many reasons. >> >>In the message above, note the use of 'improved' and 'may'. >
One more question! :)
Is it possible to use someone elses Window login through code? I think I read something about aliasing or somesuch. Don't want to go down that road myself, but I would like to know if it is possible. Thanks Vayse [quoted text, click to view] "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message news:6kfqi29ndl987jm90id91dpht1uajmde39@4ax.com... > Okay but the question was: >>When I was checking the help on enabling mixed mode login, I noticed that >> Microsoft recommends against it. It doesn't give a reason. >> Have you any idea why this is? > I didn't really see an answer to the posters question - > other than "2000 it was not very secure." > Mentioning that it has improved and may meet your needs > doesn't really address the risks that still exist, what the > issues are (which is what the poster asked) and doesn't > really leave one with the impression that it is still > recommended to use windows authentication. I wanted to make > sure the poster wasn't making any assumptions otherwise. > I would have made some bad assumptions if I didn't know a > little about SQL Server security. > > -Sue > > On Wed, 11 Oct 2006 11:41:20 -0700, "Arnie Rowland" > <arnie@1568.com> wrote: > >>Agreed. I don't use or recommend SQL Logins for many, many reasons. >> >>In the message above, note the use of 'improved' and 'may'. >
In 2005, my first guess would be that you read about using
the Execute As clause. Is this what you were thinking of: http://msdn2.microsoft.com/en-us/library/ms188354.aspx On 2000, there was setuser (which has been replaced by executed as) but it worked differently, wasn't recommended to be used, didn't support windows users - just SQL logins, only used by sysadmins, and a lot of other differences. -Sue On Thu, 12 Oct 2006 10:52:49 +0100, "Vayse" [quoted text, click to view] <vayse@deadspam.com> wrote:
>One more question! :) >Is it possible to use someone elses Window login through code? I think I >read something about aliasing or somesuch. Don't want to go down that road >myself, but I would like to know if it is possible. >Thanks >Vayse > > > >"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message >news:6kfqi29ndl987jm90id91dpht1uajmde39@4ax.com... >> Okay but the question was: >>>When I was checking the help on enabling mixed mode login, I noticed that >>> Microsoft recommends against it. It doesn't give a reason. >>> Have you any idea why this is? >> I didn't really see an answer to the posters question - >> other than "2000 it was not very secure." >> Mentioning that it has improved and may meet your needs >> doesn't really address the risks that still exist, what the >> issues are (which is what the poster asked) and doesn't >> really leave one with the impression that it is still >> recommended to use windows authentication. I wanted to make >> sure the poster wasn't making any assumptions otherwise. >> I would have made some bad assumptions if I didn't know a >> little about SQL Server security. >> >> -Sue >> >> On Wed, 11 Oct 2006 11:41:20 -0700, "Arnie Rowland" >> <arnie@1568.com> wrote: >> >>>Agreed. I don't use or recommend SQL Logins for many, many reasons. >>> >>>In the message above, note the use of 'improved' and 'may'. >> >
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |