Hey. We have an application which is opened by a user to enter in their
username and password. This data resides in SQL 2000 but I'm upgrading to SQ
L
2005. I need to store the passwords of users in an encrypted format. How
would I do this?
Can you provide with the steps for the procedure? thank you.Have you tried looking in BOL?
How To: Encrypt a Column of Data
<http://msdn2.microsoft.com/en-us/library/ms179331.aspx>
*mike hodgson*
http://sqlnerd.blogspot.com
Tejas Parikh wrote:
>Hey. We have an application which is opened by a user to enter in their
>username and password. This data resides in SQL 2000 but I'm upgrading to S
QL
>2005. I need to store the passwords of users in an encrypted format. How
>would I do this?
>Can you provide with the steps for the procedure? thank you.
>sql
Showing posts with label security. Show all posts
Showing posts with label security. Show all posts
Wednesday, March 28, 2012
Question regarding Security and user authentication
Hey. We have an application which is opened by a user to enter in their
username and password. This data resides in SQL 2000 but I'm upgrading to SQL
2005. I need to store the passwords of users in an encrypted format. How
would I do this?
Can you provide with the steps for the procedure? thank you.This is a multi-part message in MIME format.
--010100040609020901020201
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Have you tried looking in BOL?
How To: Encrypt a Column of Data
<http://msdn2.microsoft.com/en-us/library/ms179331.aspx>
--
*mike hodgson*
http://sqlnerd.blogspot.com
Tejas Parikh wrote:
>Hey. We have an application which is opened by a user to enter in their
>username and password. This data resides in SQL 2000 but I'm upgrading to SQL
>2005. I need to store the passwords of users in an encrypted format. How
>would I do this?
>Can you provide with the steps for the procedure? thank you.
>
--010100040609020901020201
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Have you tried looking in BOL?<br>
<br>
<a href="http://links.10026.com/?link=How">http://msdn2.microsoft.com/en-us/library/ms179331.aspx">How
To: Encrypt a Column of Data</a><br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Tejas Parikh wrote:
<blockquote cite="midCD30DC41-20C2-4E08-9668-87E020E95E12@.microsoft.com"
type="cite">
<pre wrap="">Hey. We have an application which is opened by a user to enter in their
username and password. This data resides in SQL 2000 but I'm upgrading to SQL
2005. I need to store the passwords of users in an encrypted format. How
would I do this?
Can you provide with the steps for the procedure? thank you.
</pre>
</blockquote>
</body>
</html>
--010100040609020901020201--
username and password. This data resides in SQL 2000 but I'm upgrading to SQL
2005. I need to store the passwords of users in an encrypted format. How
would I do this?
Can you provide with the steps for the procedure? thank you.This is a multi-part message in MIME format.
--010100040609020901020201
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Have you tried looking in BOL?
How To: Encrypt a Column of Data
<http://msdn2.microsoft.com/en-us/library/ms179331.aspx>
--
*mike hodgson*
http://sqlnerd.blogspot.com
Tejas Parikh wrote:
>Hey. We have an application which is opened by a user to enter in their
>username and password. This data resides in SQL 2000 but I'm upgrading to SQL
>2005. I need to store the passwords of users in an encrypted format. How
>would I do this?
>Can you provide with the steps for the procedure? thank you.
>
--010100040609020901020201
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Have you tried looking in BOL?<br>
<br>
<a href="http://links.10026.com/?link=How">http://msdn2.microsoft.com/en-us/library/ms179331.aspx">How
To: Encrypt a Column of Data</a><br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Tejas Parikh wrote:
<blockquote cite="midCD30DC41-20C2-4E08-9668-87E020E95E12@.microsoft.com"
type="cite">
<pre wrap="">Hey. We have an application which is opened by a user to enter in their
username and password. This data resides in SQL 2000 but I'm upgrading to SQL
2005. I need to store the passwords of users in an encrypted format. How
would I do this?
Can you provide with the steps for the procedure? thank you.
</pre>
</blockquote>
</body>
</html>
--010100040609020901020201--
Monday, March 26, 2012
Question re: security issue detailed in KB887459
Is the Report Manager vulnerable to the issue described here:
http://support.microsoft.com/?kbid=887459
I'm not very experienced with ASP.NET - can I assume that any additional
safeguards for the "canonicalization" issues would have to come from
Microsoft in the case of Report Manager as it is a compiled app? TIA.
-BAHI Brian:
There is now an MSI file that will install an HttpModule to protect
all ASP.NET applications.
See: http://www.microsoft.com/security/incident/aspnet.mspx
--
Scott
http://www.OdeToCode.com/
On Thu, 07 Oct 2004 17:00:46 -0700, Brian Almond
<pythonista@.sbcglobal.net> wrote:
>Is the Report Manager vulnerable to the issue described here:
>http://support.microsoft.com/?kbid=887459
>I'm not very experienced with ASP.NET - can I assume that any additional
>safeguards for the "canonicalization" issues would have to come from
>Microsoft in the case of Report Manager as it is a compiled app? TIA.
>-BA|||Scott Allen wrote:
> There is now an MSI file that will install an HttpModule to protect
> all ASP.NET applications.
> See: http://www.microsoft.com/security/incident/aspnet.mspx
Thanks for posting the link Scott. Unfortunately it looks like
something that MSI does has confused Report Manager on my our
development RS box so that now I'm getting a security exception when
browsing to it. Playing with it now to see if I just need to make
simple config. changes or if it's something more involved causing me
trouble.
-BA|||Interesting, I'll give it a try tommorow at home and see what happens.
--
Scott
http://www.OdeToCode.com/
On Fri, 08 Oct 2004 10:02:30 -0700, Brian Almond
<pythonista@.sbcglobal.net> wrote:
>Scott Allen wrote:
>> There is now an MSI file that will install an HttpModule to protect
>> all ASP.NET applications.
>> See: http://www.microsoft.com/security/incident/aspnet.mspx
>Thanks for posting the link Scott. Unfortunately it looks like
>something that MSI does has confused Report Manager on my our
>development RS box so that now I'm getting a security exception when
>browsing to it. Playing with it now to see if I just need to make
>simple config. changes or if it's something more involved causing me
>trouble.
>-BA|||Brian,
I had the exact same problem when I installed - let me know if you get a
resolution on this.
Thanks,
Dan
"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
> Scott Allen wrote:
> > There is now an MSI file that will install an HttpModule to protect
> > all ASP.NET applications.
> >
> > See: http://www.microsoft.com/security/incident/aspnet.mspx
> Thanks for posting the link Scott. Unfortunately it looks like
> something that MSI does has confused Report Manager on my our
> development RS box so that now I'm getting a security exception when
> browsing to it. Playing with it now to see if I just need to make
> simple config. changes or if it's something more involved causing me
> trouble.
> -BA|||Yes, it's a problem, unfortunately.
I have everything working again after adding a new CodeGroup to both
policy config files, see:
http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
Let me know if this gets you up and running again. If anyone from MS
has an official recommendation I'll update the blog.
--
Scott
http://www.OdeToCode.com/
On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
<dplaskon@.sympatico.ca> wrote:
>Brian,
>I had the exact same problem when I installed - let me know if you get a
>resolution on this.
>Thanks,
>Dan
>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||That change doesn't work on my server. I get a parse error on the
ValidatePathModule line of
machine.config... Very strange error as it only says "?" as error message.
/Per Salmi
"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
> Yes, it's a problem, unfortunately.
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
> --
> Scott
> http://www.OdeToCode.com/
> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||Tried the same thing on another server and now the parse error on
machine.config says:
Description: An error occurred during the processing of a configuration file
required to service this request. Please review the specific error details
below and modify your configuration file appropriately.
Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
permission grant set is incompatible between appdomains.
Source Error:
Line 320: <add name="FileAuthorization"
type="System.Web.Security.FileAuthorizationModule"/>
Line 321: <add name="ErrorHandlerModule"
type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
Line 322: <add name="ValidatePathModule"
type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
Version=1.0.0.0, Culture=neutral,
PublicKeyToken=eba19824f86fdadd"/></httpModules>
Line 323: <!--
Line 324: processModel Attributes:
/Per Salmi
"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
> Yes, it's a problem, unfortunately.
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
> --
> Scott
> http://www.OdeToCode.com/
> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||If you restart the web server after making the configuration changes
it should all be working then.
--
Scott
http://www.OdeToCode.com/
On Mon, 11 Oct 2004 12:11:14 +0200, "Per Salmi"
<per.salmi@.nospam.nospam> wrote:
>Tried the same thing on another server and now the parse error on
>machine.config says:
>Description: An error occurred during the processing of a configuration file
>required to service this request. Please review the specific error details
>below and modify your configuration file appropriately.
>Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
>permission grant set is incompatible between appdomains.
>Source Error:
>Line 320: <add name="FileAuthorization"
>type="System.Web.Security.FileAuthorizationModule"/>
>Line 321: <add name="ErrorHandlerModule"
>type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
>Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
>Line 322: <add name="ValidatePathModule"
>type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
>Version=1.0.0.0, Culture=neutral,
>PublicKeyToken=eba19824f86fdadd"/></httpModules>
>Line 323: <!--
>Line 324: processModel Attributes:
>
>/Per Salmi
>
>"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
>news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
>> Yes, it's a problem, unfortunately.
>> I have everything working again after adding a new CodeGroup to both
>> policy config files, see:
>> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
>> Let me know if this gets you up and running again. If anyone from MS
>> has an official recommendation I'll update the blog.
>> --
>> Scott
>> http://www.OdeToCode.com/
>> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
>> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>>
>|||I called Microsoft support services, they were clueless. I'm blogging
about it at http://www.dogcaught.com/dpack/index.php?p=52
Aaron
http://www.hockley.org
"Per Salmi" <per.salmi@.nospam.nospam> wrote in message news:<ebdglq3rEHA.192@.tk2msftngp13.phx.gbl>...
> Tried the same thing on another server and now the parse error on
> machine.config says:
> Description: An error occurred during the processing of a configuration file
> required to service this request. Please review the specific error details
> below and modify your configuration file appropriately.
> Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
> permission grant set is incompatible between appdomains.
> Source Error:
> Line 320: <add name="FileAuthorization"
> type="System.Web.Security.FileAuthorizationModule"/>
> Line 321: <add name="ErrorHandlerModule"
> type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
> Line 322: <add name="ValidatePathModule"
> type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
> Version=1.0.0.0, Culture=neutral,
> PublicKeyToken=eba19824f86fdadd"/></httpModules>
> Line 323: <!--
> Line 324: processModel Attributes:
>
> /Per Salmi
>|||Scott Allen wrote:
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
I have to admit that I'm left wondering why Microsoft released the patch
without getting a green light on their web apps. Coincidentally I
have tried your fix for their patch, but am now getting an error message
complaining about a request for StrongNameIdentityPermission. I guess
I'm going to have to bear down and study materials on configuration
ASP.NET applications if I want to get any semblance of a grip on this.
-BA|||Brian Almond wrote:
> configuration ASP.NET applications
_Configuring_ ASP.NET applications. Bah! I should really review my
messages prior to posting ;)|||Just an FYI for everyone. This doesn't help solve the issue with a system
being messed up but I don't think that RS is vulnerable to this exploit. I
have had some discussions with MS people and the bottom line is that Report
Server stores all of its secure content in the database. Report Manager is
the portal to Report Server and although it could be affected that there
would not be an exploit because it does not have secure content.
Further explanation given to me is that this exploit is seen when using form
authentication and you use the result of authentication to grant
permissions to files inside your application vroot which Report Manager does
not do.
If you think about how Report Server is designed as a service and if you
look on the server you will not see any rdl files. SQL Server db is used to
store all this information. So it is not like Report Manager is just opening
up report files.
I hesitated to jump in but I hate to see people messing around and wasting
time and energy on a patch that isn't needed. Please note that I am not the
official MS voice. At a minimum I would delay working on it. Hopefully we
can get an official MS person to bless what I said above.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
news:%23oJu899rEHA.2684@.TK2MSFTNGP12.phx.gbl...
> Scott Allen wrote:
> > I have everything working again after adding a new CodeGroup to both
> > policy config files, see:
> > http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> >
> > Let me know if this gets you up and running again. If anyone from MS
> > has an official recommendation I'll update the blog.
> I have to admit that I'm left wondering why Microsoft released the patch
> without getting a green light on their web apps. Coincidentally I
> have tried your fix for their patch, but am now getting an error message
> complaining about a request for StrongNameIdentityPermission. I guess
> I'm going to have to bear down and study materials on configuration
> ASP.NET applications if I want to get any semblance of a grip on this.
> -BA|||Bruce,
Thanks for commenting on this. I hope we do get an official word at
some point on this issue. It would be nice not to have to worry about
this issue in the future.
Unfortunately, at this point I have a 'dead' report server. Luckily
it's my test server, but I would like to get it back up without
reinstalling RS if possible. (Uninstalling the MS patch doesn't seem to
revert all of its changes.) I didn't backup all of the configuration
files before applying the Microsoft patch, so I've given myself a more
difficult restore situation than I could have had otherwise.
It is certainly time better spent elsewhere.
-BA|||Hi Bruce:
I'm inclined to agree with you after some experimenting today.
In the case where someone *has* to install the module on a machine
with SSRS (because there are other ASP.NET applications present), the
fix is to put the following entry in the web.config file (both
ReportManager and ReportServer config files) in the system.web
section:
<httpModules>
<remove name="ValidatePathModule"/>
</httpModules>
This disables the module for just the SSRS applications.
There sure has been some confusion. I've seen a couple reputable
sources say the vulnerability exists for Windows authentication in
addition to forms authentication. I've also heard that Windows 2003 is
affected, even though I haven't been able to exploit the vulnerability
on any of my 2003 machines.
--
Scott
http://www.OdeToCode.com/
On Mon, 11 Oct 2004 17:33:19 -0500, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>Just an FYI for everyone. This doesn't help solve the issue with a system
>being messed up but I don't think that RS is vulnerable to this exploit. I
>have had some discussions with MS people and the bottom line is that Report
>Server stores all of its secure content in the database. Report Manager is
>the portal to Report Server and although it could be affected that there
>would not be an exploit because it does not have secure content.
>Further explanation given to me is that this exploit is seen when using form
>authentication and you use the result of authentication to grant
>permissions to files inside your application vroot which Report Manager does
>not do.
>If you think about how Report Server is designed as a service and if you
>look on the server you will not see any rdl files. SQL Server db is used to
>store all this information. So it is not like Report Manager is just opening
>up report files.
>I hesitated to jump in but I hate to see people messing around and wasting
>time and energy on a patch that isn't needed. Please note that I am not the
>official MS voice. At a minimum I would delay working on it. Hopefully we
>can get an official MS person to bless what I said above.|||We are working on a KB article that will have the offical workaround for
this. We hope to have it posted tomorrow.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:kobmm0hepkp6gh7voej1d882b56mpbt1en@.4ax.com...
> Hi Bruce:
> I'm inclined to agree with you after some experimenting today.
> In the case where someone *has* to install the module on a machine
> with SSRS (because there are other ASP.NET applications present), the
> fix is to put the following entry in the web.config file (both
> ReportManager and ReportServer config files) in the system.web
> section:
> <httpModules>
> <remove name="ValidatePathModule"/>
> </httpModules>
> This disables the module for just the SSRS applications.
> There sure has been some confusion. I've seen a couple reputable
> sources say the vulnerability exists for Windows authentication in
> addition to forms authentication. I've also heard that Windows 2003 is
> affected, even though I haven't been able to exploit the vulnerability
> on any of my 2003 machines.
> --
> Scott
> http://www.OdeToCode.com/
> On Mon, 11 Oct 2004 17:33:19 -0500, "Bruce L-C [MVP]"
> <bruce_lcNOSPAM@.hotmail.com> wrote:
>>Just an FYI for everyone. This doesn't help solve the issue with a system
>>being messed up but I don't think that RS is vulnerable to this exploit. I
>>have had some discussions with MS people and the bottom line is that
>>Report
>>Server stores all of its secure content in the database. Report Manager is
>>the portal to Report Server and although it could be affected that there
>>would not be an exploit because it does not have secure content.
>>Further explanation given to me is that this exploit is seen when using
>>form
>>authentication and you use the result of authentication to grant
>>permissions to files inside your application vroot which Report Manager
>>does
>>not do.
>>If you think about how Report Server is designed as a service and if you
>>look on the server you will not see any rdl files. SQL Server db is used
>>to
>>store all this information. So it is not like Report Manager is just
>>opening
>>up report files.
>>I hesitated to jump in but I hate to see people messing around and wasting
>>time and energy on a patch that isn't needed. Please note that I am not
>>the
>>official MS voice. At a minimum I would delay working on it. Hopefully we
>>can get an official MS person to bless what I said above.
>|||You are right in this that the RS might not be affected by the vulnerability
but as there might be lots of other asp.net applications running on the same
server that are vulnerable it would feel better to have the patch installed,
and still have a working report server application.
Best regards,
Per Salmi
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i meddelandet
news:ODmNQJ%23rEHA.2096@.TK2MSFTNGP11.phx.gbl...
> I hesitated to jump in but I hate to see people messing around and wasting
> time and energy on a patch that isn't needed. Please note that I am not
> the
> official MS voice. At a minimum I would delay working on it. Hopefully we
> can get an official MS person to bless what I said above.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services|||There is now a KB article that describes the workaround at
http://support.microsoft.com/?kbid=887787.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Per Salmi" <per.salmi@.nospam.nospam> wrote in message
news:uBzQx2BsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> You are right in this that the RS might not be affected by the
> vulnerability but as there might be lots of other asp.net applications
> running on the same server that are vulnerable it would feel better to
> have the patch installed, and still have a working report server
> application.
> Best regards,
> Per Salmi
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i meddelandet
> news:ODmNQJ%23rEHA.2096@.TK2MSFTNGP11.phx.gbl...
>> I hesitated to jump in but I hate to see people messing around and
>> wasting
>> time and energy on a patch that isn't needed. Please note that I am not
>> the
>> official MS voice. At a minimum I would delay working on it. Hopefully we
>> can get an official MS person to bless what I said above.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>|||Thanks, Brian.
--
Scott
http://www.OdeToCode.com/
On Tue, 12 Oct 2004 16:09:32 -0700, "Brian Welcker [MSFT]"
<bwelcker@.online.microsoft.com> wrote:
>There is now a KB article that describes the workaround at
>http://support.microsoft.com/?kbid=887787.|||Thanks! That worked perfectly on both of our servers.
/Per Salmi
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> skrev i meddelandet
news:eXlZICLsEHA.2560@.tk2msftngp13.phx.gbl...
> There is now a KB article that describes the workaround at
> http://support.microsoft.com/?kbid=887787.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
http://support.microsoft.com/?kbid=887459
I'm not very experienced with ASP.NET - can I assume that any additional
safeguards for the "canonicalization" issues would have to come from
Microsoft in the case of Report Manager as it is a compiled app? TIA.
-BAHI Brian:
There is now an MSI file that will install an HttpModule to protect
all ASP.NET applications.
See: http://www.microsoft.com/security/incident/aspnet.mspx
--
Scott
http://www.OdeToCode.com/
On Thu, 07 Oct 2004 17:00:46 -0700, Brian Almond
<pythonista@.sbcglobal.net> wrote:
>Is the Report Manager vulnerable to the issue described here:
>http://support.microsoft.com/?kbid=887459
>I'm not very experienced with ASP.NET - can I assume that any additional
>safeguards for the "canonicalization" issues would have to come from
>Microsoft in the case of Report Manager as it is a compiled app? TIA.
>-BA|||Scott Allen wrote:
> There is now an MSI file that will install an HttpModule to protect
> all ASP.NET applications.
> See: http://www.microsoft.com/security/incident/aspnet.mspx
Thanks for posting the link Scott. Unfortunately it looks like
something that MSI does has confused Report Manager on my our
development RS box so that now I'm getting a security exception when
browsing to it. Playing with it now to see if I just need to make
simple config. changes or if it's something more involved causing me
trouble.
-BA|||Interesting, I'll give it a try tommorow at home and see what happens.
--
Scott
http://www.OdeToCode.com/
On Fri, 08 Oct 2004 10:02:30 -0700, Brian Almond
<pythonista@.sbcglobal.net> wrote:
>Scott Allen wrote:
>> There is now an MSI file that will install an HttpModule to protect
>> all ASP.NET applications.
>> See: http://www.microsoft.com/security/incident/aspnet.mspx
>Thanks for posting the link Scott. Unfortunately it looks like
>something that MSI does has confused Report Manager on my our
>development RS box so that now I'm getting a security exception when
>browsing to it. Playing with it now to see if I just need to make
>simple config. changes or if it's something more involved causing me
>trouble.
>-BA|||Brian,
I had the exact same problem when I installed - let me know if you get a
resolution on this.
Thanks,
Dan
"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
> Scott Allen wrote:
> > There is now an MSI file that will install an HttpModule to protect
> > all ASP.NET applications.
> >
> > See: http://www.microsoft.com/security/incident/aspnet.mspx
> Thanks for posting the link Scott. Unfortunately it looks like
> something that MSI does has confused Report Manager on my our
> development RS box so that now I'm getting a security exception when
> browsing to it. Playing with it now to see if I just need to make
> simple config. changes or if it's something more involved causing me
> trouble.
> -BA|||Yes, it's a problem, unfortunately.
I have everything working again after adding a new CodeGroup to both
policy config files, see:
http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
Let me know if this gets you up and running again. If anyone from MS
has an official recommendation I'll update the blog.
--
Scott
http://www.OdeToCode.com/
On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
<dplaskon@.sympatico.ca> wrote:
>Brian,
>I had the exact same problem when I installed - let me know if you get a
>resolution on this.
>Thanks,
>Dan
>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||That change doesn't work on my server. I get a parse error on the
ValidatePathModule line of
machine.config... Very strange error as it only says "?" as error message.
/Per Salmi
"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
> Yes, it's a problem, unfortunately.
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
> --
> Scott
> http://www.OdeToCode.com/
> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||Tried the same thing on another server and now the parse error on
machine.config says:
Description: An error occurred during the processing of a configuration file
required to service this request. Please review the specific error details
below and modify your configuration file appropriately.
Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
permission grant set is incompatible between appdomains.
Source Error:
Line 320: <add name="FileAuthorization"
type="System.Web.Security.FileAuthorizationModule"/>
Line 321: <add name="ErrorHandlerModule"
type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
Line 322: <add name="ValidatePathModule"
type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
Version=1.0.0.0, Culture=neutral,
PublicKeyToken=eba19824f86fdadd"/></httpModules>
Line 323: <!--
Line 324: processModel Attributes:
/Per Salmi
"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
> Yes, it's a problem, unfortunately.
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
> --
> Scott
> http://www.OdeToCode.com/
> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||If you restart the web server after making the configuration changes
it should all be working then.
--
Scott
http://www.OdeToCode.com/
On Mon, 11 Oct 2004 12:11:14 +0200, "Per Salmi"
<per.salmi@.nospam.nospam> wrote:
>Tried the same thing on another server and now the parse error on
>machine.config says:
>Description: An error occurred during the processing of a configuration file
>required to service this request. Please review the specific error details
>below and modify your configuration file appropriately.
>Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
>permission grant set is incompatible between appdomains.
>Source Error:
>Line 320: <add name="FileAuthorization"
>type="System.Web.Security.FileAuthorizationModule"/>
>Line 321: <add name="ErrorHandlerModule"
>type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
>Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
>Line 322: <add name="ValidatePathModule"
>type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
>Version=1.0.0.0, Culture=neutral,
>PublicKeyToken=eba19824f86fdadd"/></httpModules>
>Line 323: <!--
>Line 324: processModel Attributes:
>
>/Per Salmi
>
>"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
>news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
>> Yes, it's a problem, unfortunately.
>> I have everything working again after adding a new CodeGroup to both
>> policy config files, see:
>> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
>> Let me know if this gets you up and running again. If anyone from MS
>> has an official recommendation I'll update the blog.
>> --
>> Scott
>> http://www.OdeToCode.com/
>> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
>> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>>
>|||I called Microsoft support services, they were clueless. I'm blogging
about it at http://www.dogcaught.com/dpack/index.php?p=52
Aaron
http://www.hockley.org
"Per Salmi" <per.salmi@.nospam.nospam> wrote in message news:<ebdglq3rEHA.192@.tk2msftngp13.phx.gbl>...
> Tried the same thing on another server and now the parse error on
> machine.config says:
> Description: An error occurred during the processing of a configuration file
> required to service this request. Please review the specific error details
> below and modify your configuration file appropriately.
> Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
> permission grant set is incompatible between appdomains.
> Source Error:
> Line 320: <add name="FileAuthorization"
> type="System.Web.Security.FileAuthorizationModule"/>
> Line 321: <add name="ErrorHandlerModule"
> type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
> Line 322: <add name="ValidatePathModule"
> type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
> Version=1.0.0.0, Culture=neutral,
> PublicKeyToken=eba19824f86fdadd"/></httpModules>
> Line 323: <!--
> Line 324: processModel Attributes:
>
> /Per Salmi
>|||Scott Allen wrote:
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
I have to admit that I'm left wondering why Microsoft released the patch
without getting a green light on their web apps. Coincidentally I
have tried your fix for their patch, but am now getting an error message
complaining about a request for StrongNameIdentityPermission. I guess
I'm going to have to bear down and study materials on configuration
ASP.NET applications if I want to get any semblance of a grip on this.
-BA|||Brian Almond wrote:
> configuration ASP.NET applications
_Configuring_ ASP.NET applications. Bah! I should really review my
messages prior to posting ;)|||Just an FYI for everyone. This doesn't help solve the issue with a system
being messed up but I don't think that RS is vulnerable to this exploit. I
have had some discussions with MS people and the bottom line is that Report
Server stores all of its secure content in the database. Report Manager is
the portal to Report Server and although it could be affected that there
would not be an exploit because it does not have secure content.
Further explanation given to me is that this exploit is seen when using form
authentication and you use the result of authentication to grant
permissions to files inside your application vroot which Report Manager does
not do.
If you think about how Report Server is designed as a service and if you
look on the server you will not see any rdl files. SQL Server db is used to
store all this information. So it is not like Report Manager is just opening
up report files.
I hesitated to jump in but I hate to see people messing around and wasting
time and energy on a patch that isn't needed. Please note that I am not the
official MS voice. At a minimum I would delay working on it. Hopefully we
can get an official MS person to bless what I said above.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
news:%23oJu899rEHA.2684@.TK2MSFTNGP12.phx.gbl...
> Scott Allen wrote:
> > I have everything working again after adding a new CodeGroup to both
> > policy config files, see:
> > http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> >
> > Let me know if this gets you up and running again. If anyone from MS
> > has an official recommendation I'll update the blog.
> I have to admit that I'm left wondering why Microsoft released the patch
> without getting a green light on their web apps. Coincidentally I
> have tried your fix for their patch, but am now getting an error message
> complaining about a request for StrongNameIdentityPermission. I guess
> I'm going to have to bear down and study materials on configuration
> ASP.NET applications if I want to get any semblance of a grip on this.
> -BA|||Bruce,
Thanks for commenting on this. I hope we do get an official word at
some point on this issue. It would be nice not to have to worry about
this issue in the future.
Unfortunately, at this point I have a 'dead' report server. Luckily
it's my test server, but I would like to get it back up without
reinstalling RS if possible. (Uninstalling the MS patch doesn't seem to
revert all of its changes.) I didn't backup all of the configuration
files before applying the Microsoft patch, so I've given myself a more
difficult restore situation than I could have had otherwise.
It is certainly time better spent elsewhere.
-BA|||Hi Bruce:
I'm inclined to agree with you after some experimenting today.
In the case where someone *has* to install the module on a machine
with SSRS (because there are other ASP.NET applications present), the
fix is to put the following entry in the web.config file (both
ReportManager and ReportServer config files) in the system.web
section:
<httpModules>
<remove name="ValidatePathModule"/>
</httpModules>
This disables the module for just the SSRS applications.
There sure has been some confusion. I've seen a couple reputable
sources say the vulnerability exists for Windows authentication in
addition to forms authentication. I've also heard that Windows 2003 is
affected, even though I haven't been able to exploit the vulnerability
on any of my 2003 machines.
--
Scott
http://www.OdeToCode.com/
On Mon, 11 Oct 2004 17:33:19 -0500, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>Just an FYI for everyone. This doesn't help solve the issue with a system
>being messed up but I don't think that RS is vulnerable to this exploit. I
>have had some discussions with MS people and the bottom line is that Report
>Server stores all of its secure content in the database. Report Manager is
>the portal to Report Server and although it could be affected that there
>would not be an exploit because it does not have secure content.
>Further explanation given to me is that this exploit is seen when using form
>authentication and you use the result of authentication to grant
>permissions to files inside your application vroot which Report Manager does
>not do.
>If you think about how Report Server is designed as a service and if you
>look on the server you will not see any rdl files. SQL Server db is used to
>store all this information. So it is not like Report Manager is just opening
>up report files.
>I hesitated to jump in but I hate to see people messing around and wasting
>time and energy on a patch that isn't needed. Please note that I am not the
>official MS voice. At a minimum I would delay working on it. Hopefully we
>can get an official MS person to bless what I said above.|||We are working on a KB article that will have the offical workaround for
this. We hope to have it posted tomorrow.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:kobmm0hepkp6gh7voej1d882b56mpbt1en@.4ax.com...
> Hi Bruce:
> I'm inclined to agree with you after some experimenting today.
> In the case where someone *has* to install the module on a machine
> with SSRS (because there are other ASP.NET applications present), the
> fix is to put the following entry in the web.config file (both
> ReportManager and ReportServer config files) in the system.web
> section:
> <httpModules>
> <remove name="ValidatePathModule"/>
> </httpModules>
> This disables the module for just the SSRS applications.
> There sure has been some confusion. I've seen a couple reputable
> sources say the vulnerability exists for Windows authentication in
> addition to forms authentication. I've also heard that Windows 2003 is
> affected, even though I haven't been able to exploit the vulnerability
> on any of my 2003 machines.
> --
> Scott
> http://www.OdeToCode.com/
> On Mon, 11 Oct 2004 17:33:19 -0500, "Bruce L-C [MVP]"
> <bruce_lcNOSPAM@.hotmail.com> wrote:
>>Just an FYI for everyone. This doesn't help solve the issue with a system
>>being messed up but I don't think that RS is vulnerable to this exploit. I
>>have had some discussions with MS people and the bottom line is that
>>Report
>>Server stores all of its secure content in the database. Report Manager is
>>the portal to Report Server and although it could be affected that there
>>would not be an exploit because it does not have secure content.
>>Further explanation given to me is that this exploit is seen when using
>>form
>>authentication and you use the result of authentication to grant
>>permissions to files inside your application vroot which Report Manager
>>does
>>not do.
>>If you think about how Report Server is designed as a service and if you
>>look on the server you will not see any rdl files. SQL Server db is used
>>to
>>store all this information. So it is not like Report Manager is just
>>opening
>>up report files.
>>I hesitated to jump in but I hate to see people messing around and wasting
>>time and energy on a patch that isn't needed. Please note that I am not
>>the
>>official MS voice. At a minimum I would delay working on it. Hopefully we
>>can get an official MS person to bless what I said above.
>|||You are right in this that the RS might not be affected by the vulnerability
but as there might be lots of other asp.net applications running on the same
server that are vulnerable it would feel better to have the patch installed,
and still have a working report server application.
Best regards,
Per Salmi
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i meddelandet
news:ODmNQJ%23rEHA.2096@.TK2MSFTNGP11.phx.gbl...
> I hesitated to jump in but I hate to see people messing around and wasting
> time and energy on a patch that isn't needed. Please note that I am not
> the
> official MS voice. At a minimum I would delay working on it. Hopefully we
> can get an official MS person to bless what I said above.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services|||There is now a KB article that describes the workaround at
http://support.microsoft.com/?kbid=887787.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Per Salmi" <per.salmi@.nospam.nospam> wrote in message
news:uBzQx2BsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> You are right in this that the RS might not be affected by the
> vulnerability but as there might be lots of other asp.net applications
> running on the same server that are vulnerable it would feel better to
> have the patch installed, and still have a working report server
> application.
> Best regards,
> Per Salmi
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i meddelandet
> news:ODmNQJ%23rEHA.2096@.TK2MSFTNGP11.phx.gbl...
>> I hesitated to jump in but I hate to see people messing around and
>> wasting
>> time and energy on a patch that isn't needed. Please note that I am not
>> the
>> official MS voice. At a minimum I would delay working on it. Hopefully we
>> can get an official MS person to bless what I said above.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>|||Thanks, Brian.
--
Scott
http://www.OdeToCode.com/
On Tue, 12 Oct 2004 16:09:32 -0700, "Brian Welcker [MSFT]"
<bwelcker@.online.microsoft.com> wrote:
>There is now a KB article that describes the workaround at
>http://support.microsoft.com/?kbid=887787.|||Thanks! That worked perfectly on both of our servers.
/Per Salmi
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> skrev i meddelandet
news:eXlZICLsEHA.2560@.tk2msftngp13.phx.gbl...
> There is now a KB article that describes the workaround at
> http://support.microsoft.com/?kbid=887787.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
Tuesday, March 20, 2012
Question on security
Scenario: We have an Sql Server system and want to set up a payroll system.
We have about 30 tables in this database. We are now going to be handling
outside companies payroll information.
Setup 1: One of the fields in the tables would be a company code. This
would logically separate company A's data from company B's data.
Our question is on security. How secure is the data if it is all in one
database? If someone were to hack the database, they could potentially have
access to every companies data.
Setup 2: One way around that would be to replicate the 30 tables for each
company we bring on board. If we were to have 100 companies, we would have
100 databases and 300 tables.
This, of course, would be a problem as we make changes to the tables, since
we would have to make the changes to the same tables in each of the 100
databases.
One benefit to this setup is that if someone were to hack one database, they
wouldn't necessarilly have access to other companies data since they would
be in other database, even though all the databases reside on the same
machine under the same Sql Server.
This brings up the issue of multiple machines - which would really be going
overboard.
I am just trying to use due diligence here to see what my possible problems
are and whether we could be over thinking the problem.
Payroll data is very sensitive and we need to make sure we are going far
enough to protect it, but not too far to make it unwieldy.
The other question would be whether maybe having all the data in one
database might slow down the response too much where if we had separate
databases, searches and updates might be faster.
Just looking for ideas from others who may have wrestled with these
questions.
Thanks,
Tom.
On Wed, 1 Dec 2004 15:57:48 -0800, "tshad"
<tscheiderich@.ftsolutions.com> wrote:
>Just looking for ideas from others who may have wrestled with these
>questions.
Old questions, everyone chooses their own answers.
You can partition tables and use views to restrict users to their own
company's rows and stuff like that, if you want to keep things in a
single database and/or single tables.
There are always tradeoffs between convenience, performance, and
security. What's the old cliche, "good, fast, cheap - choose two".
J.
|||> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
>
No, don't use separate databases. Nowadays you can have a very big database
and if you properly designed it you will not have any problem in terms of
perfomance. You probably would not want to maintain many databases all
system calagos and...
If you defined indexes on the tables and do provide tuning the database
searches and updates might be faster so it has not to do with having many
databases.
Also, I'd remove a guest account and make sure that all may users have
appropriate permissions to access the tables.
Don't GRANT them access to underlying tables instead create views to select
data.
Visit on this web site to read more about security of the database
http://vyaskn.tripod.com/sql_server_..._practices.htm
http://vyaskn.tripod.com/row_level_s..._databases.htm
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OgCxbGA2EHA.2112@.TK2MSFTNGP15.phx.gbl...
> Scenario: We have an Sql Server system and want to set up a payroll
system.
> We have about 30 tables in this database. We are now going to be handling
> outside companies payroll information.
> Setup 1: One of the fields in the tables would be a company code. This
> would logically separate company A's data from company B's data.
> Our question is on security. How secure is the data if it is all in one
> database? If someone were to hack the database, they could potentially
have
> access to every companies data.
> Setup 2: One way around that would be to replicate the 30 tables for each
> company we bring on board. If we were to have 100 companies, we would
have
> 100 databases and 300 tables.
> This, of course, would be a problem as we make changes to the tables,
since
> we would have to make the changes to the same tables in each of the 100
> databases.
> One benefit to this setup is that if someone were to hack one database,
they
> wouldn't necessarilly have access to other companies data since they would
> be in other database, even though all the databases reside on the same
> machine under the same Sql Server.
> This brings up the issue of multiple machines - which would really be
going
> overboard.
> I am just trying to use due diligence here to see what my possible
problems
> are and whether we could be over thinking the problem.
> Payroll data is very sensitive and we need to make sure we are going far
> enough to protect it, but not too far to make it unwieldy.
> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
> Just looking for ideas from others who may have wrestled with these
> questions.
> Thanks,
> Tom.
>
We have about 30 tables in this database. We are now going to be handling
outside companies payroll information.
Setup 1: One of the fields in the tables would be a company code. This
would logically separate company A's data from company B's data.
Our question is on security. How secure is the data if it is all in one
database? If someone were to hack the database, they could potentially have
access to every companies data.
Setup 2: One way around that would be to replicate the 30 tables for each
company we bring on board. If we were to have 100 companies, we would have
100 databases and 300 tables.
This, of course, would be a problem as we make changes to the tables, since
we would have to make the changes to the same tables in each of the 100
databases.
One benefit to this setup is that if someone were to hack one database, they
wouldn't necessarilly have access to other companies data since they would
be in other database, even though all the databases reside on the same
machine under the same Sql Server.
This brings up the issue of multiple machines - which would really be going
overboard.
I am just trying to use due diligence here to see what my possible problems
are and whether we could be over thinking the problem.
Payroll data is very sensitive and we need to make sure we are going far
enough to protect it, but not too far to make it unwieldy.
The other question would be whether maybe having all the data in one
database might slow down the response too much where if we had separate
databases, searches and updates might be faster.
Just looking for ideas from others who may have wrestled with these
questions.
Thanks,
Tom.
On Wed, 1 Dec 2004 15:57:48 -0800, "tshad"
<tscheiderich@.ftsolutions.com> wrote:
>Just looking for ideas from others who may have wrestled with these
>questions.
Old questions, everyone chooses their own answers.
You can partition tables and use views to restrict users to their own
company's rows and stuff like that, if you want to keep things in a
single database and/or single tables.
There are always tradeoffs between convenience, performance, and
security. What's the old cliche, "good, fast, cheap - choose two".
J.
|||> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
>
No, don't use separate databases. Nowadays you can have a very big database
and if you properly designed it you will not have any problem in terms of
perfomance. You probably would not want to maintain many databases all
system calagos and...
If you defined indexes on the tables and do provide tuning the database
searches and updates might be faster so it has not to do with having many
databases.
Also, I'd remove a guest account and make sure that all may users have
appropriate permissions to access the tables.
Don't GRANT them access to underlying tables instead create views to select
data.
Visit on this web site to read more about security of the database
http://vyaskn.tripod.com/sql_server_..._practices.htm
http://vyaskn.tripod.com/row_level_s..._databases.htm
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OgCxbGA2EHA.2112@.TK2MSFTNGP15.phx.gbl...
> Scenario: We have an Sql Server system and want to set up a payroll
system.
> We have about 30 tables in this database. We are now going to be handling
> outside companies payroll information.
> Setup 1: One of the fields in the tables would be a company code. This
> would logically separate company A's data from company B's data.
> Our question is on security. How secure is the data if it is all in one
> database? If someone were to hack the database, they could potentially
have
> access to every companies data.
> Setup 2: One way around that would be to replicate the 30 tables for each
> company we bring on board. If we were to have 100 companies, we would
have
> 100 databases and 300 tables.
> This, of course, would be a problem as we make changes to the tables,
since
> we would have to make the changes to the same tables in each of the 100
> databases.
> One benefit to this setup is that if someone were to hack one database,
they
> wouldn't necessarilly have access to other companies data since they would
> be in other database, even though all the databases reside on the same
> machine under the same Sql Server.
> This brings up the issue of multiple machines - which would really be
going
> overboard.
> I am just trying to use due diligence here to see what my possible
problems
> are and whether we could be over thinking the problem.
> Payroll data is very sensitive and we need to make sure we are going far
> enough to protect it, but not too far to make it unwieldy.
> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
> Just looking for ideas from others who may have wrestled with these
> questions.
> Thanks,
> Tom.
>
Question on security
Scenario: We have an Sql Server system and want to set up a payroll system.
We have about 30 tables in this database. We are now going to be handling
outside companies payroll information.
Setup 1: One of the fields in the tables would be a company code. This
would logically separate company A's data from company B's data.
Our question is on security. How secure is the data if it is all in one
database? If someone were to hack the database, they could potentially have
access to every companies data.
Setup 2: One way around that would be to replicate the 30 tables for each
company we bring on board. If we were to have 100 companies, we would have
100 databases and 300 tables.
This, of course, would be a problem as we make changes to the tables, since
we would have to make the changes to the same tables in each of the 100
databases.
One benefit to this setup is that if someone were to hack one database, they
wouldn't necessarilly have access to other companies data since they would
be in other database, even though all the databases reside on the same
machine under the same Sql Server.
This brings up the issue of multiple machines - which would really be going
overboard.
I am just trying to use due diligence here to see what my possible problems
are and whether we could be over thinking the problem.
Payroll data is very sensitive and we need to make sure we are going far
enough to protect it, but not too far to make it unwieldy.
The other question would be whether maybe having all the data in one
database might slow down the response too much where if we had separate
databases, searches and updates might be faster.
Just looking for ideas from others who may have wrestled with these
questions.
Thanks,
Tom.On Wed, 1 Dec 2004 15:57:48 -0800, "tshad"
<tscheiderich@.ftsolutions.com> wrote:
>Just looking for ideas from others who may have wrestled with these
>questions.
Old questions, everyone chooses their own answers.
You can partition tables and use views to restrict users to their own
company's rows and stuff like that, if you want to keep things in a
single database and/or single tables.
There are always tradeoffs between convenience, performance, and
security. What's the old cliche, "good, fast, cheap - choose two".
J.|||> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
>
No, don't use separate databases. Nowadays you can have a very big database
and if you properly designed it you will not have any problem in terms of
perfomance. You probably would not want to maintain many databases all
system calagos and...
If you defined indexes on the tables and do provide tuning the database
searches and updates might be faster so it has not to do with having many
databases.
Also, I'd remove a guest account and make sure that all may users have
appropriate permissions to access the tables.
Don't GRANT them access to underlying tables instead create views to select
data.
Visit on this web site to read more about security of the database
http://vyaskn.tripod.com/sql_server...t_practices.htm
http://vyaskn.tripod.com/ row_level...as
es.htm
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OgCxbGA2EHA.2112@.TK2MSFTNGP15.phx.gbl...
> Scenario: We have an Sql Server system and want to set up a payroll
system.
> We have about 30 tables in this database. We are now going to be handling
> outside companies payroll information.
> Setup 1: One of the fields in the tables would be a company code. This
> would logically separate company A's data from company B's data.
> Our question is on security. How secure is the data if it is all in one
> database? If someone were to hack the database, they could potentially
have
> access to every companies data.
> Setup 2: One way around that would be to replicate the 30 tables for each
> company we bring on board. If we were to have 100 companies, we would
have
> 100 databases and 300 tables.
> This, of course, would be a problem as we make changes to the tables,
since
> we would have to make the changes to the same tables in each of the 100
> databases.
> One benefit to this setup is that if someone were to hack one database,
they
> wouldn't necessarilly have access to other companies data since they would
> be in other database, even though all the databases reside on the same
> machine under the same Sql Server.
> This brings up the issue of multiple machines - which would really be
going
> overboard.
> I am just trying to use due diligence here to see what my possible
problems
> are and whether we could be over thinking the problem.
> Payroll data is very sensitive and we need to make sure we are going far
> enough to protect it, but not too far to make it unwieldy.
> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
> Just looking for ideas from others who may have wrestled with these
> questions.
> Thanks,
> Tom.
>
We have about 30 tables in this database. We are now going to be handling
outside companies payroll information.
Setup 1: One of the fields in the tables would be a company code. This
would logically separate company A's data from company B's data.
Our question is on security. How secure is the data if it is all in one
database? If someone were to hack the database, they could potentially have
access to every companies data.
Setup 2: One way around that would be to replicate the 30 tables for each
company we bring on board. If we were to have 100 companies, we would have
100 databases and 300 tables.
This, of course, would be a problem as we make changes to the tables, since
we would have to make the changes to the same tables in each of the 100
databases.
One benefit to this setup is that if someone were to hack one database, they
wouldn't necessarilly have access to other companies data since they would
be in other database, even though all the databases reside on the same
machine under the same Sql Server.
This brings up the issue of multiple machines - which would really be going
overboard.
I am just trying to use due diligence here to see what my possible problems
are and whether we could be over thinking the problem.
Payroll data is very sensitive and we need to make sure we are going far
enough to protect it, but not too far to make it unwieldy.
The other question would be whether maybe having all the data in one
database might slow down the response too much where if we had separate
databases, searches and updates might be faster.
Just looking for ideas from others who may have wrestled with these
questions.
Thanks,
Tom.On Wed, 1 Dec 2004 15:57:48 -0800, "tshad"
<tscheiderich@.ftsolutions.com> wrote:
>Just looking for ideas from others who may have wrestled with these
>questions.
Old questions, everyone chooses their own answers.
You can partition tables and use views to restrict users to their own
company's rows and stuff like that, if you want to keep things in a
single database and/or single tables.
There are always tradeoffs between convenience, performance, and
security. What's the old cliche, "good, fast, cheap - choose two".
J.|||> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
>
No, don't use separate databases. Nowadays you can have a very big database
and if you properly designed it you will not have any problem in terms of
perfomance. You probably would not want to maintain many databases all
system calagos and...
If you defined indexes on the tables and do provide tuning the database
searches and updates might be faster so it has not to do with having many
databases.
Also, I'd remove a guest account and make sure that all may users have
appropriate permissions to access the tables.
Don't GRANT them access to underlying tables instead create views to select
data.
Visit on this web site to read more about security of the database
http://vyaskn.tripod.com/sql_server...t_practices.htm
http://vyaskn.tripod.com/ row_level...as
es.htm
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OgCxbGA2EHA.2112@.TK2MSFTNGP15.phx.gbl...
> Scenario: We have an Sql Server system and want to set up a payroll
system.
> We have about 30 tables in this database. We are now going to be handling
> outside companies payroll information.
> Setup 1: One of the fields in the tables would be a company code. This
> would logically separate company A's data from company B's data.
> Our question is on security. How secure is the data if it is all in one
> database? If someone were to hack the database, they could potentially
have
> access to every companies data.
> Setup 2: One way around that would be to replicate the 30 tables for each
> company we bring on board. If we were to have 100 companies, we would
have
> 100 databases and 300 tables.
> This, of course, would be a problem as we make changes to the tables,
since
> we would have to make the changes to the same tables in each of the 100
> databases.
> One benefit to this setup is that if someone were to hack one database,
they
> wouldn't necessarilly have access to other companies data since they would
> be in other database, even though all the databases reside on the same
> machine under the same Sql Server.
> This brings up the issue of multiple machines - which would really be
going
> overboard.
> I am just trying to use due diligence here to see what my possible
problems
> are and whether we could be over thinking the problem.
> Payroll data is very sensitive and we need to make sure we are going far
> enough to protect it, but not too far to make it unwieldy.
> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
> Just looking for ideas from others who may have wrestled with these
> questions.
> Thanks,
> Tom.
>
Question on security
Hi,
I have a feeling what I want to do is not possible. I have a reportserver
(everything works fine) and I have an asp.net app which allows users to run
some reports. I dont use the reportviewer control, I just opent the report in
a new IE window by using url access e.g.
http://reportserver/reports/TimeReport&rs:command=render.
This works fine but is there a way of preventing users just typing in
http://reportserver/reports into the url and therefore accessing all the
reports. I know I could use windows authentication for the reports but I cant
do that for various reasons.
Is there any setting I can set on the http://reportserver/reports site to
only allow certain users access it but yet allow url access to reports as
normal using e.g. http://reportserver/reports/TimeReport&rs:command=render.
Its a bit of a contradiction really, I want the reportsite to be secure but
yet be able to run url access reports from it.
Thanks
NYou could deny access to the root from the report manager. Just allow
access to the subfolders/individual reports. Of course, they could then
directly access the subfolder...
Mike G.
"NH" <NH@.discussions.microsoft.com> wrote in message
news:846A037D-582D-498E-A6A1-5BF59AA772AF@.microsoft.com...
> Hi,
> I have a feeling what I want to do is not possible. I have a reportserver
> (everything works fine) and I have an asp.net app which allows users to
> run
> some reports. I dont use the reportviewer control, I just opent the report
> in
> a new IE window by using url access e.g.
> http://reportserver/reports/TimeReport&rs:command=render.
> This works fine but is there a way of preventing users just typing in
> http://reportserver/reports into the url and therefore accessing all the
> reports. I know I could use windows authentication for the reports but I
> cant
> do that for various reasons.
> Is there any setting I can set on the http://reportserver/reports site to
> only allow certain users access it but yet allow url access to reports as
> normal using e.g.
> http://reportserver/reports/TimeReport&rs:command=render.
> Its a bit of a contradiction really, I want the reportsite to be secure
> but
> yet be able to run url access reports from it.
> Thanks
> N
>|||Thanks Mike G, I dont think there is a way to do this.
"Mike G." wrote:
> You could deny access to the root from the report manager. Just allow
> access to the subfolders/individual reports. Of course, they could then
> directly access the subfolder...
> Mike G.
>
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:846A037D-582D-498E-A6A1-5BF59AA772AF@.microsoft.com...
> > Hi,
> >
> > I have a feeling what I want to do is not possible. I have a reportserver
> > (everything works fine) and I have an asp.net app which allows users to
> > run
> > some reports. I dont use the reportviewer control, I just opent the report
> > in
> > a new IE window by using url access e.g.
> > http://reportserver/reports/TimeReport&rs:command=render.
> >
> > This works fine but is there a way of preventing users just typing in
> > http://reportserver/reports into the url and therefore accessing all the
> > reports. I know I could use windows authentication for the reports but I
> > cant
> > do that for various reasons.
> >
> > Is there any setting I can set on the http://reportserver/reports site to
> > only allow certain users access it but yet allow url access to reports as
> > normal using e.g.
> > http://reportserver/reports/TimeReport&rs:command=render.
> > Its a bit of a contradiction really, I want the reportsite to be secure
> > but
> > yet be able to run url access reports from it.
> >
> > Thanks
> > N
> >
> >
>
>
I have a feeling what I want to do is not possible. I have a reportserver
(everything works fine) and I have an asp.net app which allows users to run
some reports. I dont use the reportviewer control, I just opent the report in
a new IE window by using url access e.g.
http://reportserver/reports/TimeReport&rs:command=render.
This works fine but is there a way of preventing users just typing in
http://reportserver/reports into the url and therefore accessing all the
reports. I know I could use windows authentication for the reports but I cant
do that for various reasons.
Is there any setting I can set on the http://reportserver/reports site to
only allow certain users access it but yet allow url access to reports as
normal using e.g. http://reportserver/reports/TimeReport&rs:command=render.
Its a bit of a contradiction really, I want the reportsite to be secure but
yet be able to run url access reports from it.
Thanks
NYou could deny access to the root from the report manager. Just allow
access to the subfolders/individual reports. Of course, they could then
directly access the subfolder...
Mike G.
"NH" <NH@.discussions.microsoft.com> wrote in message
news:846A037D-582D-498E-A6A1-5BF59AA772AF@.microsoft.com...
> Hi,
> I have a feeling what I want to do is not possible. I have a reportserver
> (everything works fine) and I have an asp.net app which allows users to
> run
> some reports. I dont use the reportviewer control, I just opent the report
> in
> a new IE window by using url access e.g.
> http://reportserver/reports/TimeReport&rs:command=render.
> This works fine but is there a way of preventing users just typing in
> http://reportserver/reports into the url and therefore accessing all the
> reports. I know I could use windows authentication for the reports but I
> cant
> do that for various reasons.
> Is there any setting I can set on the http://reportserver/reports site to
> only allow certain users access it but yet allow url access to reports as
> normal using e.g.
> http://reportserver/reports/TimeReport&rs:command=render.
> Its a bit of a contradiction really, I want the reportsite to be secure
> but
> yet be able to run url access reports from it.
> Thanks
> N
>|||Thanks Mike G, I dont think there is a way to do this.
"Mike G." wrote:
> You could deny access to the root from the report manager. Just allow
> access to the subfolders/individual reports. Of course, they could then
> directly access the subfolder...
> Mike G.
>
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:846A037D-582D-498E-A6A1-5BF59AA772AF@.microsoft.com...
> > Hi,
> >
> > I have a feeling what I want to do is not possible. I have a reportserver
> > (everything works fine) and I have an asp.net app which allows users to
> > run
> > some reports. I dont use the reportviewer control, I just opent the report
> > in
> > a new IE window by using url access e.g.
> > http://reportserver/reports/TimeReport&rs:command=render.
> >
> > This works fine but is there a way of preventing users just typing in
> > http://reportserver/reports into the url and therefore accessing all the
> > reports. I know I could use windows authentication for the reports but I
> > cant
> > do that for various reasons.
> >
> > Is there any setting I can set on the http://reportserver/reports site to
> > only allow certain users access it but yet allow url access to reports as
> > normal using e.g.
> > http://reportserver/reports/TimeReport&rs:command=render.
> > Its a bit of a contradiction really, I want the reportsite to be secure
> > but
> > yet be able to run url access reports from it.
> >
> > Thanks
> > N
> >
> >
>
>
Question on security
Scenario: We have an Sql Server system and want to set up a payroll system.
We have about 30 tables in this database. We are now going to be handling
outside companies payroll information.
Setup 1: One of the fields in the tables would be a company code. This
would logically separate company A's data from company B's data.
Our question is on security. How secure is the data if it is all in one
database? If someone were to hack the database, they could potentially have
access to every companies data.
Setup 2: One way around that would be to replicate the 30 tables for each
company we bring on board. If we were to have 100 companies, we would have
100 databases and 300 tables.
This, of course, would be a problem as we make changes to the tables, since
we would have to make the changes to the same tables in each of the 100
databases.
One benefit to this setup is that if someone were to hack one database, they
wouldn't necessarilly have access to other companies data since they would
be in other database, even though all the databases reside on the same
machine under the same Sql Server.
This brings up the issue of multiple machines - which would really be going
overboard.
I am just trying to use due diligence here to see what my possible problems
are and whether we could be over thinking the problem.
Payroll data is very sensitive and we need to make sure we are going far
enough to protect it, but not too far to make it unwieldy.
The other question would be whether maybe having all the data in one
database might slow down the response too much where if we had separate
databases, searches and updates might be faster.
Just looking for ideas from others who may have wrestled with these
questions.
Thanks,
Tom.On Wed, 1 Dec 2004 15:57:48 -0800, "tshad"
<tscheiderich@.ftsolutions.com> wrote:
>Just looking for ideas from others who may have wrestled with these
>questions.
Old questions, everyone chooses their own answers.
You can partition tables and use views to restrict users to their own
company's rows and stuff like that, if you want to keep things in a
single database and/or single tables.
There are always tradeoffs between convenience, performance, and
security. What's the old cliche, "good, fast, cheap - choose two".
J.|||> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
>
No, don't use separate databases. Nowadays you can have a very big database
and if you properly designed it you will not have any problem in terms of
perfomance. You probably would not want to maintain many databases all
system calagos and...
If you defined indexes on the tables and do provide tuning the database
searches and updates might be faster so it has not to do with having many
databases.
Also, I'd remove a guest account and make sure that all may users have
appropriate permissions to access the tables.
Don't GRANT them access to underlying tables instead create views to select
data.
Visit on this web site to read more about security of the database
http://vyaskn.tripod.com/sql_server_security_best_practices.htm
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OgCxbGA2EHA.2112@.TK2MSFTNGP15.phx.gbl...
> Scenario: We have an Sql Server system and want to set up a payroll
system.
> We have about 30 tables in this database. We are now going to be handling
> outside companies payroll information.
> Setup 1: One of the fields in the tables would be a company code. This
> would logically separate company A's data from company B's data.
> Our question is on security. How secure is the data if it is all in one
> database? If someone were to hack the database, they could potentially
have
> access to every companies data.
> Setup 2: One way around that would be to replicate the 30 tables for each
> company we bring on board. If we were to have 100 companies, we would
have
> 100 databases and 300 tables.
> This, of course, would be a problem as we make changes to the tables,
since
> we would have to make the changes to the same tables in each of the 100
> databases.
> One benefit to this setup is that if someone were to hack one database,
they
> wouldn't necessarilly have access to other companies data since they would
> be in other database, even though all the databases reside on the same
> machine under the same Sql Server.
> This brings up the issue of multiple machines - which would really be
going
> overboard.
> I am just trying to use due diligence here to see what my possible
problems
> are and whether we could be over thinking the problem.
> Payroll data is very sensitive and we need to make sure we are going far
> enough to protect it, but not too far to make it unwieldy.
> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
> Just looking for ideas from others who may have wrestled with these
> questions.
> Thanks,
> Tom.
>
We have about 30 tables in this database. We are now going to be handling
outside companies payroll information.
Setup 1: One of the fields in the tables would be a company code. This
would logically separate company A's data from company B's data.
Our question is on security. How secure is the data if it is all in one
database? If someone were to hack the database, they could potentially have
access to every companies data.
Setup 2: One way around that would be to replicate the 30 tables for each
company we bring on board. If we were to have 100 companies, we would have
100 databases and 300 tables.
This, of course, would be a problem as we make changes to the tables, since
we would have to make the changes to the same tables in each of the 100
databases.
One benefit to this setup is that if someone were to hack one database, they
wouldn't necessarilly have access to other companies data since they would
be in other database, even though all the databases reside on the same
machine under the same Sql Server.
This brings up the issue of multiple machines - which would really be going
overboard.
I am just trying to use due diligence here to see what my possible problems
are and whether we could be over thinking the problem.
Payroll data is very sensitive and we need to make sure we are going far
enough to protect it, but not too far to make it unwieldy.
The other question would be whether maybe having all the data in one
database might slow down the response too much where if we had separate
databases, searches and updates might be faster.
Just looking for ideas from others who may have wrestled with these
questions.
Thanks,
Tom.On Wed, 1 Dec 2004 15:57:48 -0800, "tshad"
<tscheiderich@.ftsolutions.com> wrote:
>Just looking for ideas from others who may have wrestled with these
>questions.
Old questions, everyone chooses their own answers.
You can partition tables and use views to restrict users to their own
company's rows and stuff like that, if you want to keep things in a
single database and/or single tables.
There are always tradeoffs between convenience, performance, and
security. What's the old cliche, "good, fast, cheap - choose two".
J.|||> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
>
No, don't use separate databases. Nowadays you can have a very big database
and if you properly designed it you will not have any problem in terms of
perfomance. You probably would not want to maintain many databases all
system calagos and...
If you defined indexes on the tables and do provide tuning the database
searches and updates might be faster so it has not to do with having many
databases.
Also, I'd remove a guest account and make sure that all may users have
appropriate permissions to access the tables.
Don't GRANT them access to underlying tables instead create views to select
data.
Visit on this web site to read more about security of the database
http://vyaskn.tripod.com/sql_server_security_best_practices.htm
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OgCxbGA2EHA.2112@.TK2MSFTNGP15.phx.gbl...
> Scenario: We have an Sql Server system and want to set up a payroll
system.
> We have about 30 tables in this database. We are now going to be handling
> outside companies payroll information.
> Setup 1: One of the fields in the tables would be a company code. This
> would logically separate company A's data from company B's data.
> Our question is on security. How secure is the data if it is all in one
> database? If someone were to hack the database, they could potentially
have
> access to every companies data.
> Setup 2: One way around that would be to replicate the 30 tables for each
> company we bring on board. If we were to have 100 companies, we would
have
> 100 databases and 300 tables.
> This, of course, would be a problem as we make changes to the tables,
since
> we would have to make the changes to the same tables in each of the 100
> databases.
> One benefit to this setup is that if someone were to hack one database,
they
> wouldn't necessarilly have access to other companies data since they would
> be in other database, even though all the databases reside on the same
> machine under the same Sql Server.
> This brings up the issue of multiple machines - which would really be
going
> overboard.
> I am just trying to use due diligence here to see what my possible
problems
> are and whether we could be over thinking the problem.
> Payroll data is very sensitive and we need to make sure we are going far
> enough to protect it, but not too far to make it unwieldy.
> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
> Just looking for ideas from others who may have wrestled with these
> questions.
> Thanks,
> Tom.
>
Friday, March 9, 2012
Question on Internal Activation Stored Procedure Security Context
CLR function has the following few lines which is invoked from Internal Activation Stored Procedure:
SqlCommand command = Connection.CreateCommand();
command.CommandText = "CREATE ASSEMBLY " + "\"" + AsmName + "\"" +" AUTHORIZATION [dbo]"+ " FROM " + "'" + regasm.UncPath + "'" + " WITH PERMISSION_SET=SAFE";
command.ExecuteNonQuery();
I am getting the following error:
"Could not impersonate the client during assembly file operation."
The CLR function is invoked from Service Broker internal activation stored procedure.
"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.
SqlContext.WindowsIdentity.Name is "NT AUTHORITY\SYSTEM" as the Data Engine runs with the LocalSystem account.
How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?
Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".
Many Thanks.
You have to mark the database trustworthy. Because the activated procedure is under an EXECUTE AS context, you are seeing all the problems described here: http://msdn2.microsoft.com/en-us/library/ms188304.aspx
HTH,
~ Remus
Thanks for your reply. I have done "SET TRUSTWORTHY ON" to the DB initiating the dialog. But still "Could not impersonate client" error is thrown. I am using CERTIFICATES for dialog security. I have few "SELECT" statements inside the CLR Stored procedure, they execute fine; Looks CREATE ASSEMBLY is denied in this security context.
Will switching from 'NT AUTHORITY\SYSTEM' to a domain account just before "CREATE ASSEMBLY" will help ?
|||The problem is not related to activation, but to EXECUTE AS USER = '...' context. The same error is returned if you run
EXECUTE AS USER='dbo';
and then try to run the CREATE ASSEMBLY statement (e.g. from a SQL Server Management Studio query window).
Under this context, after the database is marked trusthworthy, the CREATE ASSEMBLY succeeds if the login that 'dbo' is mapped to is made member of sysadmin server role.
HTH,
~ Remus
|||
BTW, if you need to avoid the syadmin membership requirement, the easiest workaround is to create the assembly from the assembly bits, not from a file. Since you're talking about an activated proc, I assume the assembly bits are available as a message payload (otherwise I really don't see the need to create an assembly in an activated procedure).
HTH,
~ Remu
Yes..As you mentioned within SQL Server management studio, I had tried earlier and it works - with "EXECUTE AS USER = domain account" as well. But within CLR proc it fails. I tried including "EXECUTE AS" inside the transact SQL batch, the error remains.
I am creating the assembly from a network path (just sending this path as a broker message), so I may not be able to use assembly bits.
|||Hi to all,
I am facing the same problem with the following line of
//code which i am trying
CREATE ASSEMBLY MyAssembly FROM 'C:\Documents and Settings\Administrator.ORC80\My Documents\Visual Studio 2005\Projects\MyDB1\MyDB1\bin\Debug\MyDB1.dll'
WITH PERMISSION_SET=SAFE
GO
//error i am getting
Msg 6585, Level 16, State 1, Line 1
Could not impersonate the client during assembly file operation.
please somebody help me...
|||Is the database marked as TRUSTWORTHY ?
Question on Internal Activation Stored Procedure Security Context
CLR function has the following few lines which is invoked from Internal Activation Stored Procedure:
SqlCommand command = Connection.CreateCommand();
command.CommandText = "CREATE ASSEMBLY " + "\"" + AsmName + "\"" +" AUTHORIZATION [dbo]"+ " FROM " + "'" + regasm.UncPath + "'" + " WITH PERMISSION_SET=SAFE";
command.ExecuteNonQuery();
I am getting the following error:
"Could not impersonate the client during assembly file operation."
The CLR function is invoked from Service Broker internal activation stored procedure.
"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.
SqlContext.WindowsIdentity.Name is "NT AUTHORITY\SYSTEM" as the Data Engine runs with the LocalSystem account.
How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?
Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".
Many Thanks.
You have to mark the database trustworthy. Because the activated procedure is under an EXECUTE AS context, you are seeing all the problems described here: http://msdn2.microsoft.com/en-us/library/ms188304.aspx
HTH,
~ Remus
Thanks for your reply. I have done "SET TRUSTWORTHY ON" to the DB initiating the dialog. But still "Could not impersonate client" error is thrown. I am using CERTIFICATES for dialog security. I have few "SELECT" statements inside the CLR Stored procedure, they execute fine; Looks CREATE ASSEMBLY is denied in this security context.
Will switching from 'NT AUTHORITY\SYSTEM' to a domain account just before "CREATE ASSEMBLY" will help ?
|||The problem is not related to activation, but to EXECUTE AS USER = '...' context. The same error is returned if you run
EXECUTE AS USER='dbo';
and then try to run the CREATE ASSEMBLY statement (e.g. from a SQL Server Management Studio query window).
Under this context, after the database is marked trusthworthy, the CREATE ASSEMBLY succeeds if the login that 'dbo' is mapped to is made member of sysadmin server role.
HTH,
~ Remus
BTW, if you need to avoid the syadmin membership requirement, the easiest workaround is to create the assembly from the assembly bits, not from a file. Since you're talking about an activated proc, I assume the assembly bits are available as a message payload (otherwise I really don't see the need to create an assembly in an activated procedure).
HTH,
~ Remu
Yes..As you mentioned within SQL Server management studio, I had tried earlier and it works - with "EXECUTE AS USER = domain account" as well. But within CLR proc it fails. I tried including "EXECUTE AS" inside the transact SQL batch, the error remains.
I am creating the assembly from a network path (just sending this path as a broker message), so I may not be able to use assembly bits.
|||Hi to all,
I am facing the same problem with the following line of
//code which i am trying
CREATE ASSEMBLY MyAssembly FROM 'C:\Documents and Settings\Administrator.ORC80\My Documents\Visual Studio 2005\Projects\MyDB1\MyDB1\bin\Debug\MyDB1.dll'
WITH PERMISSION_SET=SAFE
GO
//error i am getting
Msg 6585, Level 16, State 1, Line 1
Could not impersonate the client during assembly file operation.
please somebody help me...
|||Is the database marked as TRUSTWORTHY ?
Saturday, February 25, 2012
Question on database/table priviledges with sql server
Hi
I'll have probably to use sql server soon but prior to that I have a
question concerning priviledges and security.
Is it possible for someone to do like in access, ie creating a
db/table that is locked with a password? My guess is that it will be
yes but in cas of... Now is it possible for someone to make a db/table
read only rather than to lock it totally?
Also can the guy who has an administrator priviledge on the server
determine easily what is the password for a db even if he's not the
guy who created it?
thanksOn 30 Aug 2004 17:31:13 -0700, J.Beaulieu wrote:
>Hi
> I'll have probably to use sql server soon but prior to that I have a
>question concerning priviledges and security.
>Is it possible for someone to do like in access, ie creating a
>db/table that is locked with a password? My guess is that it will be
>yes but in cas of... Now is it possible for someone to make a db/table
>read only rather than to lock it totally?
>Also can the guy who has an administrator priviledge on the server
>determine easily what is the password for a db even if he's not the
>guy who created it?
>thanks
Hi J.,
The answer to all these questions is "no".
Access is a great tool - but comparing SQL Server to it is dangerous and
often misleading. Access control for different users in SQL Server is
completely different (and lots more professional) than anything Access has
to offer. I'll try to give you a brief overview.
A SQL Server database is a collection of tables, views, stored procedures,
triggers, etc. It can roughly be compared to an Access .mdf file. One SQL
Server server can host many SQL Server databases.
To gain access to a SQL Server database, you must first log in to the
server. There are two variations:
1. Trusted connection: SQL Server communicates with the Windows operating
system to find out what domain you are logged in to and what user name you
have. If you are user foo on domain bar, SQL Server will check if access
to the server is allowed for user bar\foo.
2. SQL Server login: You provide a name and a password. SQL Server checks
if the name supplied has access to the server and if the password matches.
The password is stored in encrypted form and I have never heard of a case
where the password was decrypted.
A new SQL Server installation will only allow access for administrators;
all other users can only gain access if someone has given them access to
the server.
Once you are logged in to the server, SQL Server will check which of the
databases on the server you may access. It is possible (though not
obvious) to find out which databases exist on a server, but it's
impossible to access databases unless someone has allowed you access.
Further access control within the database is also possible. For each
table or view, permission to insert, delete, update or select (view) rows
can be granted to (or revoked from) individual users. For update and
select permissions, this can even be drilled down to the column level (eg.
allow the managers to see rows in the Personnel table, but not the column
holding the Salary). For stored procedures, permission to execute can be
given to or taken from users. Other notable permissions are the permission
to create new objects in the database or the permission to grant
permissions to other users.
If you have many users, managing permissions is easier if you set up
roles. An example: you set up a role "Auditor". Now, you can grant select
permission deny insert, update and delete permissions on all tables and
all views to this role. Whenever a new auditor is hired, the administrator
merely has to add the Auditor role to the userid of the new auditor and
(s)he has read access to all data.
There's lots more to SQL Server security than this, but I'll stop her for
now. If you want to find out more, read about it in Books Online. It's on
your computer if you have installed SQL Server. If it isn't, you can also
find it online:
http://msdn.microsoft.com/library/e...asp?frame=true
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||jfbeaulieu2003@.yahoo.com (J.Beaulieu) wrote in message news:<eb1b8a74.0408301631.5a911870@.posting.google.com>...
> Hi
> I'll have probably to use sql server soon but prior to that I have a
> question concerning priviledges and security.
> Is it possible for someone to do like in access, ie creating a
> db/table that is locked with a password? My guess is that it will be
> yes but in cas of... Now is it possible for someone to make a db/table
> read only rather than to lock it totally?
> Also can the guy who has an administrator priviledge on the server
> determine easily what is the password for a db even if he's not the
> guy who created it?
> thanks
In MSSQL, you control access to databases and tables with permissions.
First, you grant someone access to the server, then to databases, and
finally to individual objects (not just tables). If you want a
read-only table, for example, you only GRANT SELECT, you don't GRANT
UPDATE.
A member of the sysadmin role can do anything in any database, but
there are other roles which provide more restricted permissions.
Someone can be in the db_owner role for database A, and be able to do
anything in that database, but without access to database B he can't
do anything there.
Check out the "Managing Security" section of Books Online, and
especially "Managing Permissions" to get more information. Make sure
you look at the section called "Using Ownership Chains", as it's a
very important part of MSSQL security.
Simon
Subscribe to:
Posts (Atom)