Help with Custom Security Extension and Application Pool Identity

This is an interesting one.
We have implemented a custom security extension that we call from
within an application. We pass in a userid and password into the
LogonUser method, which is then checked against our database.
However, we have a Master database, and then a few other small
databases for different clients, so the extension uses another
supplied value to go to the master database, and lookup which database
it needs to verify the user in.
So, in the RSReportServer.config file, we store the connection string
to the Master DB.
This is used to connect to the Master DB, and then lookup the
connection string for the secondary DB.
The connection string for the secondary DB uses Integrated
Authentication (as our application requires this).
When the Application Pool (in IIS) that ReportServer runs under, is
set to the NetworkUser, we get an error returned from the WebService
(called from within our application), which says that the Network
Service was not authorized to access the secondary DB. Understandable
:)
So, we changed the Application Pool, so that it uses a Domain account
as it's Identity. Now we receive the following error back (and in all
the log files I can find)..
System.Web.Services.Protocols.SoapException: An internal error
occurred on the report server. See the error log for more details.
--> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for
more details. --> System.IO.FileNotFoundException: The system cannot
find the file specified. at
System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo) at
RSManagedCrypto.RSCrypto.ExportPublicKey() at
Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
at Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
at Microsoft.ReportingServices.Library.Storage.get_Connection() at
Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
storedProcedureName) at
Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(String
key) at Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty(String
name) at Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
name) at Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(String
name) at Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
source) at Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path, Boolean validate, Boolean convert, Boolean translate) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path) at Microsoft.ReportingServices.Diagnostics.CatalogItemContext..ctor(IPathTranslator
pathTranslator, String userSuppliedPath, String parameterName) at
Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
String operation, SearchCondition[] properties) -- End of inner
exception stack trace -- at
Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
String operation, SearchCondition[] properties) at
Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
Conditions, CatalogItem[]& Items) -- End of inner exception stack
trace -- at Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
Conditions, CatalogItem[]& Items)
The Application Pool User is a member of the IIS_WPG group, and has
been granted write access to the Windows\Temp folder, and the MSSQL
folder where RS is installed (and sub folders).
I also tried adding it to the local machine admin group, and it made
no difference.
That user also has DBO access to all the DBs on our database server.
Can anyone help please?
Thanks
RichardWill you send the report server web service log file?
Also, can you verify that the new identity has a user profile on the machine
(the user should have an entry under c:\Documents and Settings)?
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Richard Greenwell" <lazygun@.gmail.com> wrote in message
news:42b7583d.0406251224.18af1b8@.posting.google.com...
> This is an interesting one.
> We have implemented a custom security extension that we call from
> within an application. We pass in a userid and password into the
> LogonUser method, which is then checked against our database.
> However, we have a Master database, and then a few other small
> databases for different clients, so the extension uses another
> supplied value to go to the master database, and lookup which database
> it needs to verify the user in.
> So, in the RSReportServer.config file, we store the connection string
> to the Master DB.
> This is used to connect to the Master DB, and then lookup the
> connection string for the secondary DB.
> The connection string for the secondary DB uses Integrated
> Authentication (as our application requires this).
> When the Application Pool (in IIS) that ReportServer runs under, is
> set to the NetworkUser, we get an error returned from the WebService
> (called from within our application), which says that the Network
> Service was not authorized to access the secondary DB. Understandable
> :)
> So, we changed the Application Pool, so that it uses a Domain account
> as it's Identity. Now we receive the following error back (and in all
> the log files I can find)..
> System.Web.Services.Protocols.SoapException: An internal error
> occurred on the report server. See the error log for more details.
> -->
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
> more details. --> System.IO.FileNotFoundException: The system cannot
> find the file specified. at
> System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo) at
> RSManagedCrypto.RSCrypto.ExportPublicKey() at
> Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
> at
Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
> at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
> at Microsoft.ReportingServices.Library.Storage.get_Connection() at
> Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
> storedProcedureName) at
>
Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(Stri
ng
> key) at
Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty
(String
> name) at
Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
> name) at
Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(Stri
ng
> name) at
Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
> at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
> source) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path, Boolean validate, Boolean convert, Boolean translate) at
> Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext..ctor(IPathTransl
ator
> pathTranslator, String userSuppliedPath, String parameterName) at
> Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
> String operation, SearchCondition[] properties) -- End of inner
> exception stack trace -- at
> Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
> String operation, SearchCondition[] properties) at
> Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
> Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
> Conditions, CatalogItem[]& Items) -- End of inner exception stack
> trace -- at
Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
> Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
> Conditions, CatalogItem[]& Items)
> The Application Pool User is a member of the IIS_WPG group, and has
> been granted write access to the Windows\Temp folder, and the MSSQL
> folder where RS is installed (and sub folders).
> I also tried adding it to the local machine admin group, and it made
> no difference.
> That user also has DBO access to all the DBs on our database server.
> Can anyone help please?
> Thanks
> Richard|||I have emailed you the log file for this problem.
The user that the application pool runs under does not have an entry
under Docs and Settings, but it is a different user to that which the
Report Server Windows Service runs under, which Does have an entry
under Docs and Settings.
I have never seen an app pool user have a Docs and Settings folder :)
Thank you
Richard
"Brian Hartman [MSFT]" <brianhartman@.hotmail.com> wrote in message news:<OTBYLlYXEHA.748@.TK2MSFTNGP11.phx.gbl>...
> Will you send the report server web service log file?
> Also, can you verify that the new identity has a user profile on the machine
> (the user should have an entry under c:\Documents and Settings)?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
>
<snip>sql

HELP SQLServer will start, SQL Agent won't

We just had a security fix applied to the server.
When we restarted both SQL Server and SQL Server agent
started. However shortly after, the SQLServerAgent stopped
running. They are both using the same account, but when
you try and start up the SQL Server Agent you get the
following error messages...
[165] ODBC Error: 0, Cannot generate SSPI context
[SQLSTATE HY000]
[000] Unable to connect to server '(local)';
SQLServerAgent cannot start
I have read the KB#811889, but was looking for anyone who
has experienced this issue.
Thanks
Fredtake a look at following article:
http://support.microsoft.com/default.aspx?scid=kb;en-
us;811889
hth
>--Original Message--
>We just had a security fix applied to the server.
>When we restarted both SQL Server and SQL Server agent
>started. However shortly after, the SQLServerAgent
stopped
>running. They are both using the same account, but when
>you try and start up the SQL Server Agent you get the
>following error messages...
>[165] ODBC Error: 0, Cannot generate SSPI context
>[SQLSTATE HY000]
>[000] Unable to connect to server '(local)';
>SQLServerAgent cannot start
>I have read the KB#811889, but was looking for anyone who
>has experienced this issue.
>Thanks
>Fred
>.
>

Help SQL 2K Security :-) DBA's

Hello everyone,
Here goes another wired on... Currently I have a local test SQL server setup
on my PC. I need to give access to another (entry, verrrrrryyyy entry level)
programmer access to my local server (READ ONLY). but here goes the problem,
I discovered that the employee replicated my db and left it open causing
security issues, since the data contains lots of sensitive data. (credit
cards, soc sec, emp names, etc). Is theirs a way in SQL server to assign
field level security? for example : I want to give him access to employee
names in the employee db, but I don't want him to be able to view employees
socials? the same with customers and credit card information or to restrict
him from exporting any data/scripts. any ideas'
Thanks in advance
AlexThank u!!!!
"alex" <hjhjjhhj@.ghghhg.com> wrote in message
news:%23LWLPaEWDHA.1480@.tk2msftngp13.phx.gbl...
> Hello everyone,
> Here goes another wired on... Currently I have a local test SQL server
setup
> on my PC. I need to give access to another (entry, verrrrrryyyy entry
level)
> programmer access to my local server (READ ONLY). but here goes the
problem,
> I discovered that the employee replicated my db and left it open causing
> security issues, since the data contains lots of sensitive data. (credit
> cards, soc sec, emp names, etc). Is theirs a way in SQL server to assign
> field level security? for example : I want to give him access to employee
> names in the employee db, but I don't want him to be able to view
employees
> socials? the same with customers and credit card information or to
restrict
> him from exporting any data/scripts. any ideas'
> Thanks in advance
> Alex
>|||"alex" <hjhjjhhj@.ghghhg.com> wrote in message
news:eVCvrHFWDHA.2008@.TK2MSFTNGP11.phx.gbl...
> Thank u!!!!
>
> "alex" <hjhjjhhj@.ghghhg.com> wrote in message
> news:%23LWLPaEWDHA.1480@.tk2msftngp13.phx.gbl...
> > Hello everyone,
> >
> > Here goes another wired on... Currently I have a local test SQL server
> setup
> > on my PC. I need to give access to another (entry, verrrrrryyyy entry
> level)
> > programmer access to my local server (READ ONLY). but here goes the
> problem,
> > I discovered that the employee replicated my db and left it open causing
> > security issues, since the data contains lots of sensitive data. (credit
> > cards, soc sec, emp names, etc). Is theirs a way in SQL server to assign
> > field level security? for example : I want to give him access to
employee
> > names in the employee db, but I don't want him to be able to view
> employees
> > socials? the same with customers and credit card information or to
> restrict
> > him from exporting any data/scripts. any ideas'
> >
> > Thanks in advance
> >
> > Alex
> >
> >
I have to add one more note to this even though you've gotten your answer
and probably moved on. You said you have a "local test SQL server" setup.
You should never have legitimate, sensitive customer/employee information in
a test database. There's just no need for it, and you're taking a huge risk
of that information falling into the wrong hands. Scramble or remove the
data in the sensitive columns immediately.
If you need a legitimate CCN or SSN for testing (such as validation
routines) then pull out your wallet and use your own. Risking compromise of
a customer or employee's personal information is just insane.
Ryan LaNeve

Help reqd on permission & security

Hi Every body,
I have newly joined this group. I am new to DB administration.
I wanted some information as to if my Server crashes (which has) & i reinstall SQL server, will restoring master database restore all my permissions & security which was set before crash. It would be great in anybody can help me on this.
Regards,
KrishnaRestoring MASTER database (rebuilding and then restoring) would get you back with your logins, default database and language assignments, and fixed server roles. The rest is stored in user databases.