Hi,
Before installing Reporting Services SP1, I can deploy and view reports.
Everything works fine. However, after I installed Reporting Services SP1, I
can not deploy all my reports to Report Servers. On the other hand, I can
preview the reports through Visual Studio .NET and it looks very normal.
Can anybody give me some suggestions?
Thanks a lot.
DavidHi,
The problem was fixed after re-installing enterprise version Reporting
Services and SP1.
"David" wrote:
> Hi,
> Before installing Reporting Services SP1, I can deploy and view reports.
> Everything works fine. However, after I installed Reporting Services SP1, I
> can not deploy all my reports to Report Servers. On the other hand, I can
> preview the reports through Visual Studio .NET and it looks very normal.
> Can anybody give me some suggestions?
> Thanks a lot.
> David
Showing posts with label reporting. Show all posts
Showing posts with label reporting. Show all posts
Friday, March 23, 2012
Failed to decrypt symmetric key....
Hi people
My IT department have reinforced at change of domain and username upon my
Reporting Services/SQL 2000 server.
At first everything seemed to be working much to my suprise. But I later
found out that all the scheduled emails ect doesnt work anymore. At closer
inspection I find this entry in the log files:
Source:
Schedule and Delivery Processor
Message:
Failed to decrypt symmetric key. Most likely cause is the service username
has changed.
Would it help reapplying the encryption key? Or would a reinstall of
Reporting Services do the trick or'
Thanks.
Regards
JonasTry reapplying the encryption key. If you don't have it, or it fails,
creating a new symetric key usually does the trick.
Kaisa M. Lindahl Lervik
"Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
news:1AAC27B6-DAE7-495D-A24B-EFC033A586F6@.microsoft.com...
> Hi people
> My IT department have reinforced at change of domain and username upon my
> Reporting Services/SQL 2000 server.
> At first everything seemed to be working much to my suprise. But I later
> found out that all the scheduled emails ect doesnt work anymore. At closer
> inspection I find this entry in the log files:
> Source:
> Schedule and Delivery Processor
> Message:
> Failed to decrypt symmetric key. Most likely cause is the service username
> has changed.
> Would it help reapplying the encryption key? Or would a reinstall of
> Reporting Services do the trick or'
> Thanks.
> Regards
> Jonassql
My IT department have reinforced at change of domain and username upon my
Reporting Services/SQL 2000 server.
At first everything seemed to be working much to my suprise. But I later
found out that all the scheduled emails ect doesnt work anymore. At closer
inspection I find this entry in the log files:
Source:
Schedule and Delivery Processor
Message:
Failed to decrypt symmetric key. Most likely cause is the service username
has changed.
Would it help reapplying the encryption key? Or would a reinstall of
Reporting Services do the trick or'
Thanks.
Regards
JonasTry reapplying the encryption key. If you don't have it, or it fails,
creating a new symetric key usually does the trick.
Kaisa M. Lindahl Lervik
"Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
news:1AAC27B6-DAE7-495D-A24B-EFC033A586F6@.microsoft.com...
> Hi people
> My IT department have reinforced at change of domain and username upon my
> Reporting Services/SQL 2000 server.
> At first everything seemed to be working much to my suprise. But I later
> found out that all the scheduled emails ect doesnt work anymore. At closer
> inspection I find this entry in the log files:
> Source:
> Schedule and Delivery Processor
> Message:
> Failed to decrypt symmetric key. Most likely cause is the service username
> has changed.
> Would it help reapplying the encryption key? Or would a reinstall of
> Reporting Services do the trick or'
> Thanks.
> Regards
> Jonassql
Wednesday, March 21, 2012
Failed to access IIS metabase?
Please help!
All of sudden, I cannot connect to the reporting services through MS SQL Server management studio. error message is "Failed to access IIS metabase".
As I checked the system:
1. reporting services is working properly (status is "started")
2. cannot access http://localhost/ReportServer$SQL2005 , the same error
3. Run:"C:\>cscript metaacl.vbs IIS://localhost/w3svc", it shows:
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
XP\ASPNET
Access: R E
BUILTIN\Administrators
Access: RWSUED
Everyone
Access: E
XP\VS Developers
Access: RWSUE
XP\ASPNET
Access: R
XP\ASPNET
Access: R
I reinstall IIS and it doesn't work. Please help, thank you so much!
Are you able to access http://localhost/reportserver from IE? Are you able to access any web pages from IE?|||Thank you for your reply.
The first answer is no, I cannot access http://localhost/reportserver from IE(The instance of SQL server 2005 is "SQL2005".)
The second answer is yes, I can access IIS' default web page and the other web pages.
|||Anybody can help, please?|||I think you need to figure out what are the required ACLs. Maybe you can start by giving everybody full access. If that works start reducing the permissions.
BTW, I thought the error message would give you more details about what ACLs are missing, but I could be wrong.
|||One more thing, if you uninstalled IIS, you may run into some problem when you try to uninstall RS later on: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=735b5260-f6bf-4ea5-94b2-6c41ae387a9aFailed to access IIS metabase
I have installed SQL Server 2005, including Reporting Services 2005 on
Windows XP Professional in a standalone environment. I can see the Reports
and ReportServer folders in IIS in Administrative Tools and all settings
appear to be appropriate.
My first problem is that, when I try to access
"http://localhost/ReportServer", I get an error "Failed to access IIS
metabase". I have looked at KB article
"http://support.microsoft.com/?kbid=267904" which is referred to in the error
message but it appears to relate to Windows Server 2003.
Secondly, I expected to get the same error when trying to access
"http://localhost/Reports" but in this case I get a message "Directory
Listing Denied" which seems to indicate that the metabase is not an issue
here. I can see the directory listing if I activiate this option in IIS in
Admin Tools but I can't browse the contents of the Reports web page.
Can anyone provide me with a solution? I have also listed this problem in
Windows Server - IIS in case it is an IIS problem.
Thank you.The ASP.NET 2.0 is not registered properly in IIS. RS2005 uses ASP.NET 2.0
Try registering in the command line using the following ..
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i
and reset the iis. I think that will solve all the problem.
Let me know.
Thanks
Bava
"NeilW" wrote:
> I have installed SQL Server 2005, including Reporting Services 2005 on
> Windows XP Professional in a standalone environment. I can see the Reports
> and ReportServer folders in IIS in Administrative Tools and all settings
> appear to be appropriate.
> My first problem is that, when I try to access
> "http://localhost/ReportServer", I get an error "Failed to access IIS
> metabase". I have looked at KB article
> "http://support.microsoft.com/?kbid=267904" which is referred to in the error
> message but it appears to relate to Windows Server 2003.
> Secondly, I expected to get the same error when trying to access
> "http://localhost/Reports" but in this case I get a message "Directory
> Listing Denied" which seems to indicate that the metabase is not an issue
> here. I can see the directory listing if I activiate this option in IIS in
> Admin Tools but I can't browse the contents of the Reports web page.
> Can anyone provide me with a solution? I have also listed this problem in
> Windows Server - IIS in case it is an IIS problem.
> Thank you.
>|||Your solution worked perfectly. Thanks very much.
"Bava Mani" wrote:
> The ASP.NET 2.0 is not registered properly in IIS. RS2005 uses ASP.NET 2.0
> Try registering in the command line using the following ..
> C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i
> and reset the iis. I think that will solve all the problem.
> Let me know.
> Thanks
> Bava
> "NeilW" wrote:
> > I have installed SQL Server 2005, including Reporting Services 2005 on
> > Windows XP Professional in a standalone environment. I can see the Reports
> > and ReportServer folders in IIS in Administrative Tools and all settings
> > appear to be appropriate.
> >
> > My first problem is that, when I try to access
> > "http://localhost/ReportServer", I get an error "Failed to access IIS
> > metabase". I have looked at KB article
> > "http://support.microsoft.com/?kbid=267904" which is referred to in the error
> > message but it appears to relate to Windows Server 2003.
> >
> > Secondly, I expected to get the same error when trying to access
> > "http://localhost/Reports" but in this case I get a message "Directory
> > Listing Denied" which seems to indicate that the metabase is not an issue
> > here. I can see the directory listing if I activiate this option in IIS in
> > Admin Tools but I can't browse the contents of the Reports web page.
> >
> > Can anyone provide me with a solution? I have also listed this problem in
> > Windows Server - IIS in case it is an IIS problem.
> >
> > Thank you.
> >|||I have this same problem, however the solution given here did not work. Here
is what I have done:
1) Run a repair install of .Net 2.0
2) Totally reinstall .Net 2.0
3) Run aspnet_regiis.exe -i
4) Run aspnet_regiis.exe -ga ASPNET
Still have the problem. Any more ideas?
--
James
"Bava Mani" wrote:
> The ASP.NET 2.0 is not registered properly in IIS. RS2005 uses ASP.NET 2.0
> Try registering in the command line using the following ..
> C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i
> and reset the iis. I think that will solve all the problem.
> Let me know.
> Thanks
> Bava
> "NeilW" wrote:
> > I have installed SQL Server 2005, including Reporting Services 2005 on
> > Windows XP Professional in a standalone environment. I can see the Reports
> > and ReportServer folders in IIS in Administrative Tools and all settings
> > appear to be appropriate.
> >
> > My first problem is that, when I try to access
> > "http://localhost/ReportServer", I get an error "Failed to access IIS
> > metabase". I have looked at KB article
> > "http://support.microsoft.com/?kbid=267904" which is referred to in the error
> > message but it appears to relate to Windows Server 2003.
> >
> > Secondly, I expected to get the same error when trying to access
> > "http://localhost/Reports" but in this case I get a message "Directory
> > Listing Denied" which seems to indicate that the metabase is not an issue
> > here. I can see the directory listing if I activiate this option in IIS in
> > Admin Tools but I can't browse the contents of the Reports web page.
> >
> > Can anyone provide me with a solution? I have also listed this problem in
> > Windows Server - IIS in case it is an IIS problem.
> >
> > Thank you.
> >|||Can you give me the error msg that it says. send me more details of when and
where you get the error.
Thanks
Bava
"James" wrote:
> I have this same problem, however the solution given here did not work. Here
> is what I have done:
> 1) Run a repair install of .Net 2.0
> 2) Totally reinstall .Net 2.0
> 3) Run aspnet_regiis.exe -i
> 4) Run aspnet_regiis.exe -ga ASPNET
> Still have the problem. Any more ideas?
> --
> James
>
> "Bava Mani" wrote:
> > The ASP.NET 2.0 is not registered properly in IIS. RS2005 uses ASP.NET 2.0
> > Try registering in the command line using the following ..
> > C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i
> > and reset the iis. I think that will solve all the problem.
> > Let me know.
> >
> > Thanks
> > Bava
> >
> > "NeilW" wrote:
> >
> > > I have installed SQL Server 2005, including Reporting Services 2005 on
> > > Windows XP Professional in a standalone environment. I can see the Reports
> > > and ReportServer folders in IIS in Administrative Tools and all settings
> > > appear to be appropriate.
> > >
> > > My first problem is that, when I try to access
> > > "http://localhost/ReportServer", I get an error "Failed to access IIS
> > > metabase". I have looked at KB article
> > > "http://support.microsoft.com/?kbid=267904" which is referred to in the error
> > > message but it appears to relate to Windows Server 2003.
> > >
> > > Secondly, I expected to get the same error when trying to access
> > > "http://localhost/Reports" but in this case I get a message "Directory
> > > Listing Denied" which seems to indicate that the metabase is not an issue
> > > here. I can see the directory listing if I activiate this option in IIS in
> > > Admin Tools but I can't browse the contents of the Reports web page.
> > >
> > > Can anyone provide me with a solution? I have also listed this problem in
> > > Windows Server - IIS in case it is an IIS problem.
> > >
> > > Thank you.
> > >
Windows XP Professional in a standalone environment. I can see the Reports
and ReportServer folders in IIS in Administrative Tools and all settings
appear to be appropriate.
My first problem is that, when I try to access
"http://localhost/ReportServer", I get an error "Failed to access IIS
metabase". I have looked at KB article
"http://support.microsoft.com/?kbid=267904" which is referred to in the error
message but it appears to relate to Windows Server 2003.
Secondly, I expected to get the same error when trying to access
"http://localhost/Reports" but in this case I get a message "Directory
Listing Denied" which seems to indicate that the metabase is not an issue
here. I can see the directory listing if I activiate this option in IIS in
Admin Tools but I can't browse the contents of the Reports web page.
Can anyone provide me with a solution? I have also listed this problem in
Windows Server - IIS in case it is an IIS problem.
Thank you.The ASP.NET 2.0 is not registered properly in IIS. RS2005 uses ASP.NET 2.0
Try registering in the command line using the following ..
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i
and reset the iis. I think that will solve all the problem.
Let me know.
Thanks
Bava
"NeilW" wrote:
> I have installed SQL Server 2005, including Reporting Services 2005 on
> Windows XP Professional in a standalone environment. I can see the Reports
> and ReportServer folders in IIS in Administrative Tools and all settings
> appear to be appropriate.
> My first problem is that, when I try to access
> "http://localhost/ReportServer", I get an error "Failed to access IIS
> metabase". I have looked at KB article
> "http://support.microsoft.com/?kbid=267904" which is referred to in the error
> message but it appears to relate to Windows Server 2003.
> Secondly, I expected to get the same error when trying to access
> "http://localhost/Reports" but in this case I get a message "Directory
> Listing Denied" which seems to indicate that the metabase is not an issue
> here. I can see the directory listing if I activiate this option in IIS in
> Admin Tools but I can't browse the contents of the Reports web page.
> Can anyone provide me with a solution? I have also listed this problem in
> Windows Server - IIS in case it is an IIS problem.
> Thank you.
>|||Your solution worked perfectly. Thanks very much.
"Bava Mani" wrote:
> The ASP.NET 2.0 is not registered properly in IIS. RS2005 uses ASP.NET 2.0
> Try registering in the command line using the following ..
> C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i
> and reset the iis. I think that will solve all the problem.
> Let me know.
> Thanks
> Bava
> "NeilW" wrote:
> > I have installed SQL Server 2005, including Reporting Services 2005 on
> > Windows XP Professional in a standalone environment. I can see the Reports
> > and ReportServer folders in IIS in Administrative Tools and all settings
> > appear to be appropriate.
> >
> > My first problem is that, when I try to access
> > "http://localhost/ReportServer", I get an error "Failed to access IIS
> > metabase". I have looked at KB article
> > "http://support.microsoft.com/?kbid=267904" which is referred to in the error
> > message but it appears to relate to Windows Server 2003.
> >
> > Secondly, I expected to get the same error when trying to access
> > "http://localhost/Reports" but in this case I get a message "Directory
> > Listing Denied" which seems to indicate that the metabase is not an issue
> > here. I can see the directory listing if I activiate this option in IIS in
> > Admin Tools but I can't browse the contents of the Reports web page.
> >
> > Can anyone provide me with a solution? I have also listed this problem in
> > Windows Server - IIS in case it is an IIS problem.
> >
> > Thank you.
> >|||I have this same problem, however the solution given here did not work. Here
is what I have done:
1) Run a repair install of .Net 2.0
2) Totally reinstall .Net 2.0
3) Run aspnet_regiis.exe -i
4) Run aspnet_regiis.exe -ga ASPNET
Still have the problem. Any more ideas?
--
James
"Bava Mani" wrote:
> The ASP.NET 2.0 is not registered properly in IIS. RS2005 uses ASP.NET 2.0
> Try registering in the command line using the following ..
> C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i
> and reset the iis. I think that will solve all the problem.
> Let me know.
> Thanks
> Bava
> "NeilW" wrote:
> > I have installed SQL Server 2005, including Reporting Services 2005 on
> > Windows XP Professional in a standalone environment. I can see the Reports
> > and ReportServer folders in IIS in Administrative Tools and all settings
> > appear to be appropriate.
> >
> > My first problem is that, when I try to access
> > "http://localhost/ReportServer", I get an error "Failed to access IIS
> > metabase". I have looked at KB article
> > "http://support.microsoft.com/?kbid=267904" which is referred to in the error
> > message but it appears to relate to Windows Server 2003.
> >
> > Secondly, I expected to get the same error when trying to access
> > "http://localhost/Reports" but in this case I get a message "Directory
> > Listing Denied" which seems to indicate that the metabase is not an issue
> > here. I can see the directory listing if I activiate this option in IIS in
> > Admin Tools but I can't browse the contents of the Reports web page.
> >
> > Can anyone provide me with a solution? I have also listed this problem in
> > Windows Server - IIS in case it is an IIS problem.
> >
> > Thank you.
> >|||Can you give me the error msg that it says. send me more details of when and
where you get the error.
Thanks
Bava
"James" wrote:
> I have this same problem, however the solution given here did not work. Here
> is what I have done:
> 1) Run a repair install of .Net 2.0
> 2) Totally reinstall .Net 2.0
> 3) Run aspnet_regiis.exe -i
> 4) Run aspnet_regiis.exe -ga ASPNET
> Still have the problem. Any more ideas?
> --
> James
>
> "Bava Mani" wrote:
> > The ASP.NET 2.0 is not registered properly in IIS. RS2005 uses ASP.NET 2.0
> > Try registering in the command line using the following ..
> > C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i
> > and reset the iis. I think that will solve all the problem.
> > Let me know.
> >
> > Thanks
> > Bava
> >
> > "NeilW" wrote:
> >
> > > I have installed SQL Server 2005, including Reporting Services 2005 on
> > > Windows XP Professional in a standalone environment. I can see the Reports
> > > and ReportServer folders in IIS in Administrative Tools and all settings
> > > appear to be appropriate.
> > >
> > > My first problem is that, when I try to access
> > > "http://localhost/ReportServer", I get an error "Failed to access IIS
> > > metabase". I have looked at KB article
> > > "http://support.microsoft.com/?kbid=267904" which is referred to in the error
> > > message but it appears to relate to Windows Server 2003.
> > >
> > > Secondly, I expected to get the same error when trying to access
> > > "http://localhost/Reports" but in this case I get a message "Directory
> > > Listing Denied" which seems to indicate that the metabase is not an issue
> > > here. I can see the directory listing if I activiate this option in IIS in
> > > Admin Tools but I can't browse the contents of the Reports web page.
> > >
> > > Can anyone provide me with a solution? I have also listed this problem in
> > > Windows Server - IIS in case it is an IIS problem.
> > >
> > > Thank you.
> > >
Friday, March 9, 2012
Fail to install reporting service
I need to install the Reporting Service in a production site on the web server.
Both web server and database server are mamber server in the same domain.
When i install the Reporting Service in the web server in keep having below
error
SQL Setup failed to connect to the database service for server
configuration. The error was: Login failed for user '(null)'.
Reason: Not associated with a trusted SQL Server connection.
Refer to the Server error logs and setup logs for more detailed information.
The eviroment configuration as below
DC : NT 4.0 with sp 6a
Web Server : - Windows 2003 standard server with sp 1, SSL Apply, Sharepoint
Services with sp 1 installed, .Net Framework 1.1 with sp 1
Database Server : Windows 2003 standard server with sp 1, MS SQL 2000 with
sp 3a in mix mode, .Net Framework 1.1 with sp 1
User :
SetupAdmin
- Domain User
- Is added to local administrator group on both web server and database
server
- With database server right on 'Security Administrator', 'Process
Administrator' and 'Database Creator'
ReportUser
- Domain User
- Is a database user
ReportSQLUser
- SQL Login Account
I login web server using SetupAdmin, when run setup. I set 'Network\Service
account' to reporting service, when ask for the database connection user I
can use either SetupAdmin, ReportUser or ReportSQLUser it will pass. But the
setup give me an error when going to setup database on the database server,
non off the 3 user account i can use to make the setup success.
Can anyone let me know why? and how to solve it?James,
Go to the SQL Server that you're trying to set up the database on, open it
up in Enterprise Manager, navigate to the Security folder and verify that the
Admin account you are using is mapped to a SQL Server login AND that that
login has the rights to create a new database. You'll need DBOwner database
role and DBCreator server role.
I believe the reason you're unable to complete your setup is a simple
permissions issue.
Hope that helps!
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"James Hong" wrote:
> I need to install the Reporting Service in a production site on the web server.
> Both web server and database server are mamber server in the same domain.
> When i install the Reporting Service in the web server in keep having below
> error
> SQL Setup failed to connect to the database service for server
> configuration. The error was: Login failed for user '(null)'.
> Reason: Not associated with a trusted SQL Server connection.
> Refer to the Server error logs and setup logs for more detailed information.
> The eviroment configuration as below
> DC : NT 4.0 with sp 6a
> Web Server : - Windows 2003 standard server with sp 1, SSL Apply, Sharepoint
> Services with sp 1 installed, .Net Framework 1.1 with sp 1
> Database Server : Windows 2003 standard server with sp 1, MS SQL 2000 with
> sp 3a in mix mode, .Net Framework 1.1 with sp 1
> User :
> SetupAdmin
> - Domain User
> - Is added to local administrator group on both web server and database
> server
> - With database server right on 'Security Administrator', 'Process
> Administrator' and 'Database Creator'
> ReportUser
> - Domain User
> - Is a database user
> ReportSQLUser
> - SQL Login Account
> I login web server using SetupAdmin, when run setup. I set 'Network\Service
> account' to reporting service, when ask for the database connection user I
> can use either SetupAdmin, ReportUser or ReportSQLUser it will pass. But the
> setup give me an error when going to setup database on the database server,
> non off the 3 user account i can use to make the setup success.
> Can anyone let me know why? and how to solve it?|||Thanks for the advice Catadmin.
Need comfirmation on the setting you have advice.
1. The Reporting Services Database name "ReportServer" and
"ReportServerTempDB" have not yet being created so the DBOwner Database Role
should grant to which database? Is the 'master', 'model' or 'msdb'.
2. I have granted the 'Database Creator' server role to SetupAdmin. So, is
it meet the DBCreator server role which you have advice?
--
Best Regard,
James Hong
"Catadmin" wrote:
> James,
> Go to the SQL Server that you're trying to set up the database on, open it
> up in Enterprise Manager, navigate to the Security folder and verify that the
> Admin account you are using is mapped to a SQL Server login AND that that
> login has the rights to create a new database. You'll need DBOwner database
> role and DBCreator server role.
> I believe the reason you're unable to complete your setup is a simple
> permissions issue.
> Hope that helps!
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
>
> "James Hong" wrote:
> > I need to install the Reporting Service in a production site on the web server.
> > Both web server and database server are mamber server in the same domain.
> > When i install the Reporting Service in the web server in keep having below
> > error
> >
> > SQL Setup failed to connect to the database service for server
> > configuration. The error was: Login failed for user '(null)'.
> > Reason: Not associated with a trusted SQL Server connection.
> > Refer to the Server error logs and setup logs for more detailed information.
> >
> > The eviroment configuration as below
> > DC : NT 4.0 with sp 6a
> > Web Server : - Windows 2003 standard server with sp 1, SSL Apply, Sharepoint
> > Services with sp 1 installed, .Net Framework 1.1 with sp 1
> > Database Server : Windows 2003 standard server with sp 1, MS SQL 2000 with
> > sp 3a in mix mode, .Net Framework 1.1 with sp 1
> > User :
> > SetupAdmin
> > - Domain User
> > - Is added to local administrator group on both web server and database
> > server
> > - With database server right on 'Security Administrator', 'Process
> > Administrator' and 'Database Creator'
> > ReportUser
> > - Domain User
> > - Is a database user
> > ReportSQLUser
> > - SQL Login Account
> >
> > I login web server using SetupAdmin, when run setup. I set 'Network\Service
> > account' to reporting service, when ask for the database connection user I
> > can use either SetupAdmin, ReportUser or ReportSQLUser it will pass. But the
> > setup give me an error when going to setup database on the database server,
> > non off the 3 user account i can use to make the setup success.
> >
> > Can anyone let me know why? and how to solve it?
Both web server and database server are mamber server in the same domain.
When i install the Reporting Service in the web server in keep having below
error
SQL Setup failed to connect to the database service for server
configuration. The error was: Login failed for user '(null)'.
Reason: Not associated with a trusted SQL Server connection.
Refer to the Server error logs and setup logs for more detailed information.
The eviroment configuration as below
DC : NT 4.0 with sp 6a
Web Server : - Windows 2003 standard server with sp 1, SSL Apply, Sharepoint
Services with sp 1 installed, .Net Framework 1.1 with sp 1
Database Server : Windows 2003 standard server with sp 1, MS SQL 2000 with
sp 3a in mix mode, .Net Framework 1.1 with sp 1
User :
SetupAdmin
- Domain User
- Is added to local administrator group on both web server and database
server
- With database server right on 'Security Administrator', 'Process
Administrator' and 'Database Creator'
ReportUser
- Domain User
- Is a database user
ReportSQLUser
- SQL Login Account
I login web server using SetupAdmin, when run setup. I set 'Network\Service
account' to reporting service, when ask for the database connection user I
can use either SetupAdmin, ReportUser or ReportSQLUser it will pass. But the
setup give me an error when going to setup database on the database server,
non off the 3 user account i can use to make the setup success.
Can anyone let me know why? and how to solve it?James,
Go to the SQL Server that you're trying to set up the database on, open it
up in Enterprise Manager, navigate to the Security folder and verify that the
Admin account you are using is mapped to a SQL Server login AND that that
login has the rights to create a new database. You'll need DBOwner database
role and DBCreator server role.
I believe the reason you're unable to complete your setup is a simple
permissions issue.
Hope that helps!
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"James Hong" wrote:
> I need to install the Reporting Service in a production site on the web server.
> Both web server and database server are mamber server in the same domain.
> When i install the Reporting Service in the web server in keep having below
> error
> SQL Setup failed to connect to the database service for server
> configuration. The error was: Login failed for user '(null)'.
> Reason: Not associated with a trusted SQL Server connection.
> Refer to the Server error logs and setup logs for more detailed information.
> The eviroment configuration as below
> DC : NT 4.0 with sp 6a
> Web Server : - Windows 2003 standard server with sp 1, SSL Apply, Sharepoint
> Services with sp 1 installed, .Net Framework 1.1 with sp 1
> Database Server : Windows 2003 standard server with sp 1, MS SQL 2000 with
> sp 3a in mix mode, .Net Framework 1.1 with sp 1
> User :
> SetupAdmin
> - Domain User
> - Is added to local administrator group on both web server and database
> server
> - With database server right on 'Security Administrator', 'Process
> Administrator' and 'Database Creator'
> ReportUser
> - Domain User
> - Is a database user
> ReportSQLUser
> - SQL Login Account
> I login web server using SetupAdmin, when run setup. I set 'Network\Service
> account' to reporting service, when ask for the database connection user I
> can use either SetupAdmin, ReportUser or ReportSQLUser it will pass. But the
> setup give me an error when going to setup database on the database server,
> non off the 3 user account i can use to make the setup success.
> Can anyone let me know why? and how to solve it?|||Thanks for the advice Catadmin.
Need comfirmation on the setting you have advice.
1. The Reporting Services Database name "ReportServer" and
"ReportServerTempDB" have not yet being created so the DBOwner Database Role
should grant to which database? Is the 'master', 'model' or 'msdb'.
2. I have granted the 'Database Creator' server role to SetupAdmin. So, is
it meet the DBCreator server role which you have advice?
--
Best Regard,
James Hong
"Catadmin" wrote:
> James,
> Go to the SQL Server that you're trying to set up the database on, open it
> up in Enterprise Manager, navigate to the Security folder and verify that the
> Admin account you are using is mapped to a SQL Server login AND that that
> login has the rights to create a new database. You'll need DBOwner database
> role and DBCreator server role.
> I believe the reason you're unable to complete your setup is a simple
> permissions issue.
> Hope that helps!
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
>
> "James Hong" wrote:
> > I need to install the Reporting Service in a production site on the web server.
> > Both web server and database server are mamber server in the same domain.
> > When i install the Reporting Service in the web server in keep having below
> > error
> >
> > SQL Setup failed to connect to the database service for server
> > configuration. The error was: Login failed for user '(null)'.
> > Reason: Not associated with a trusted SQL Server connection.
> > Refer to the Server error logs and setup logs for more detailed information.
> >
> > The eviroment configuration as below
> > DC : NT 4.0 with sp 6a
> > Web Server : - Windows 2003 standard server with sp 1, SSL Apply, Sharepoint
> > Services with sp 1 installed, .Net Framework 1.1 with sp 1
> > Database Server : Windows 2003 standard server with sp 1, MS SQL 2000 with
> > sp 3a in mix mode, .Net Framework 1.1 with sp 1
> > User :
> > SetupAdmin
> > - Domain User
> > - Is added to local administrator group on both web server and database
> > server
> > - With database server right on 'Security Administrator', 'Process
> > Administrator' and 'Database Creator'
> > ReportUser
> > - Domain User
> > - Is a database user
> > ReportSQLUser
> > - SQL Login Account
> >
> > I login web server using SetupAdmin, when run setup. I set 'Network\Service
> > account' to reporting service, when ask for the database connection user I
> > can use either SetupAdmin, ReportUser or ReportSQLUser it will pass. But the
> > setup give me an error when going to setup database on the database server,
> > non off the 3 user account i can use to make the setup success.
> >
> > Can anyone let me know why? and how to solve it?
Wednesday, March 7, 2012
Facing problem with SSRS using Temporary tables in stored procedures
Hi,
I am facing a problem using temporary tables in SQL Server stored
procedures when using them in SQL Server Reporting Services.
It gives an error while fetching fields when creating a dataset in
report designer of MS Visual Studio.Net 2005.
Has anybody else faced the same problem?
Regards,
SunnyWhat's the specific error it's producing? You can see this if the procedure
raises errors or you do alot of SELECT...INTOs rather than declaring your
temporary tables.
-T
"sunny" <sunny.mohan@.gmail.com> wrote in message
news:1148742869.635884.186170@.38g2000cwa.googlegroups.com...
> Hi,
> I am facing a problem using temporary tables in SQL Server stored
> procedures when using them in SQL Server Reporting Services.
> It gives an error while fetching fields when creating a dataset in
> report designer of MS Visual Studio.Net 2005.
> Has anybody else faced the same problem?
> Regards,
> Sunny
>|||Temporarily modify your procedure to create a table. Use the temporary procedure to populate your dataset. After SSRS has the metadata from the table, you can drop the table and restore your original procudure.
From http://www.developmentnow.com/g/115_2006_5_0_0_763042/Facing-problem-with-SSRS-using-Temporary-tables-in-stored-procedures.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||I used temporary tables in my stored procedures just fine without going
through this monkey business. One of the biggest problems is that users do
not change their command type to stored procedure!
If they did so, then it works just fine. I.e. when you create a dataset,
specify that the command type is StoredProcedure instead of the default
which is text. Then just type in the name of the stored procedure.
The other thing is to make sure they ARE running the latest Service Pack for
RS.
=-Chris
"Jeff" <nospam@.developmentnow.com> wrote in message
news:e6b4a988-3741-40f4-8359-487d262ce05c@.developmentnow.com...
> Temporarily modify your procedure to create a table. Use the temporary
> procedure to populate your dataset. After SSRS has the metadata from the
> table, you can drop the table and restore your original procudure.
> From
> http://www.developmentnow.com/g/115_2006_5_0_0_763042/Facing-problem-with-SSRS-using-Temporary-tables-in-stored-procedures.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||I'm having the same problem and the drop-down list for Command Type is greyed out, so I'm unable to change the default command type of text. How can I activate the command type drop-down list
From http://www.developmentnow.com/g/115_2006_5_0_0_763042/Facing-problem-with-SSRS-using-Temporary-tables-in-stored-procedures.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com
I am facing a problem using temporary tables in SQL Server stored
procedures when using them in SQL Server Reporting Services.
It gives an error while fetching fields when creating a dataset in
report designer of MS Visual Studio.Net 2005.
Has anybody else faced the same problem?
Regards,
SunnyWhat's the specific error it's producing? You can see this if the procedure
raises errors or you do alot of SELECT...INTOs rather than declaring your
temporary tables.
-T
"sunny" <sunny.mohan@.gmail.com> wrote in message
news:1148742869.635884.186170@.38g2000cwa.googlegroups.com...
> Hi,
> I am facing a problem using temporary tables in SQL Server stored
> procedures when using them in SQL Server Reporting Services.
> It gives an error while fetching fields when creating a dataset in
> report designer of MS Visual Studio.Net 2005.
> Has anybody else faced the same problem?
> Regards,
> Sunny
>|||Temporarily modify your procedure to create a table. Use the temporary procedure to populate your dataset. After SSRS has the metadata from the table, you can drop the table and restore your original procudure.
From http://www.developmentnow.com/g/115_2006_5_0_0_763042/Facing-problem-with-SSRS-using-Temporary-tables-in-stored-procedures.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||I used temporary tables in my stored procedures just fine without going
through this monkey business. One of the biggest problems is that users do
not change their command type to stored procedure!
If they did so, then it works just fine. I.e. when you create a dataset,
specify that the command type is StoredProcedure instead of the default
which is text. Then just type in the name of the stored procedure.
The other thing is to make sure they ARE running the latest Service Pack for
RS.
=-Chris
"Jeff" <nospam@.developmentnow.com> wrote in message
news:e6b4a988-3741-40f4-8359-487d262ce05c@.developmentnow.com...
> Temporarily modify your procedure to create a table. Use the temporary
> procedure to populate your dataset. After SSRS has the metadata from the
> table, you can drop the table and restore your original procudure.
> From
> http://www.developmentnow.com/g/115_2006_5_0_0_763042/Facing-problem-with-SSRS-using-Temporary-tables-in-stored-procedures.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||I'm having the same problem and the drop-down list for Command Type is greyed out, so I'm unable to change the default command type of text. How can I activate the command type drop-down list
From http://www.developmentnow.com/g/115_2006_5_0_0_763042/Facing-problem-with-SSRS-using-Temporary-tables-in-stored-procedures.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com
Sunday, February 26, 2012
Extremely slow report rendering - PLEASE HELP!
Hi,
We are running Reporting Services 2005 32-bit edition on a Windows 2003 R2
x64 platform, and SQL Server 2005 64-bit database server on Windows 2003 R2
x64. The Reporting Services catalog and the reporting datasource database
are both hosted remotely on the database server.
We are using the Reporting Services ReportViewer web-forms control version
8.0.50727.817 in our ASP.NET 2.0 32-bit web application. We are using custom
forms-authentication.
We have tested this same setup all hosted on a single machine, and it
flies - even with complex reports we can render a report in a couple of
seconds, so we are confident that our report queries and the database itself
are not an issue.
HOWEVER, as soon as we move the RS catalog database to the remote database
server (where it needs to be in our production environment), performance
becomes unacceptably slow - reports that renedered in a couple of seconds on
a single machine typically take 10 to 20 times longer to render.
Please, any advice on what could be causing this problem will be gratefully
received. We have explored things including network addressing, and ensured
that our database server name (which Reporting Services is configured to use
to talk to the database server) has an entry in the internal DNS.
Chris LewisHello Chris,
I understand that when you run the report on a x64 server which also host
the database, you get the report render slow.
You may run the following SQL Statement for the report server database.
select CAST(C.Name AS VARCHAR(20)) [ReportName],
E.TimeDataRetrieval,
E.TimeProcessing,
E.TimeRendering,
E.ByteCount,
E.[RowCount]
from executionlog E inner join catalog C
on E.ReportID = C.ItemID
Please let me know the result so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
We have been doing further testing, and we are 90% sure that custom (forms)
authentication and report access checking is at the root of the problem - I
think the local/remote catalog issue that I originally raised was a
red-herring, so please ignore this. We ran the query you supplied, and the
results showed that our database and the queries were performing well - no
problems with getting data for reports.
So, here is where we believe the problems lie...We have an Internet-facing
ASP.NET application that uses Reporting Services, and we have over 5000
individual users on our SQL Server database - each is registered with a
unique user ID on Reporting Services. When our custom authentication
components are called by SSRS to check access to reports, resources, etc.
for the logged-in user the access check is taking a long time to execute,
and we think we know why - here is a snippet of the code (taken from the
SSRS Forms authentication sample code, which we are using to a large extent)
in Authorization.cs which is a class within our custom authentication
extension component that implements IAuthorizationExtension. The key issue
is the foreach(AceStruct ace in acl) loop, which iterates once for each SSRS
user - and with 5000+ users (and growing rapidly) this is impacting
performance in a major way:
public bool CheckAccess(
string userName,
IntPtr userToken,
byte[] secDesc,
CatalogOperation requiredOperation)
{
AceCollection acl = DeserializeAcl(secDesc);
foreach(AceStruct ace in acl)
{
// First check to see if the user or group has an access control
// entry for the item
if (0 == String.Compare(userName, ace.PrincipalName, true,
CultureInfo.CurrentCulture))
{
// If an entry is found,
// return true if the given required operation
// is contained in the ACE structure
foreach(CatalogOperation aclOperation in ace.CatalogOperations)
{
if (aclOperation == requiredOperation)
return true;
}
}
}
return false;
}
My question is, how can we do this better and more efficiently? These access
checks are performed multiple times for each report request, so one question
I have is is there a way we can cache the results of a CheckAccess call for
a user, so we don't have to iterate through all the users again for that
user's session?
We need a solution that will scale for unlimited users.
Thanks for any advice.
Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:MvBzX9OIIHA.4268@.TK2MSFTNGHUB02.phx.gbl...
> Hello Chris,
> I understand that when you run the report on a x64 server which also host
> the database, you get the report render slow.
> You may run the following SQL Statement for the report server database.
> select CAST(C.Name AS VARCHAR(20)) [ReportName],
> E.TimeDataRetrieval,
> E.TimeProcessing,
> E.TimeRendering,
> E.ByteCount,
> E.[RowCount]
> from executionlog E inner join catalog C
> on E.ReportID = C.ItemID
>
> Please let me know the result so that I can provide further assistance.
>
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi,
(Sorry for repeat post)
We have been doing further testing, and we are 90% sure that custom (forms)
authentication and report access checking is at the root of the problem - I
think the local/remote catalog issue that I originally raised was a
red-herring, so please ignore this. We ran the query you supplied, and the
results showed that our database and the queries were performing well - no
problems with getting data for reports.
So, here is where we believe the problems lie...We have an Internet-facing
ASP.NET application that uses Reporting Services, and we have over 5000
individual users on our SQL Server database - each is registered with a
unique user ID on Reporting Services. When our custom authentication
components are called by SSRS to check access to reports, resources, etc.
for the logged-in user the access check is taking a long time to execute,
and we think we know why - here is a snippet of the code (taken from the
SSRS Forms authentication sample code, which we are using to a large extent)
in Authorization.cs which is a class within our custom authentication
extension component that implements IAuthorizationExtension. The key issue
is the foreach(AceStruct ace in acl) loop, which iterates once for each SSRS
user - and with 5000+ users (and growing rapidly) this is impacting
performance in a major way:
public bool CheckAccess(
string userName,
IntPtr userToken,
byte[] secDesc,
CatalogOperation requiredOperation)
{
AceCollection acl = DeserializeAcl(secDesc);
foreach(AceStruct ace in acl)
{
// First check to see if the user or group has an access control
// entry for the item
if (0 == String.Compare(userName, ace.PrincipalName, true,
CultureInfo.CurrentCulture))
{
// If an entry is found,
// return true if the given required operation
// is contained in the ACE structure
foreach(CatalogOperation aclOperation in ace.CatalogOperations)
{
if (aclOperation == requiredOperation)
return true;
}
}
}
return false;
}
My question is, how can we do this better and more efficiently? These access
checks are performed multiple times for each report request, so one question
I have is is there a way we can cache the results of a CheckAccess call for
a user, so we don't have to iterate through all the users again for that
user's session?
We need a solution that will scale for unlimited users.
Thanks for any advice.
Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:MvBzX9OIIHA.4268@.TK2MSFTNGHUB02.phx.gbl...
> Hello Chris,
> I understand that when you run the report on a x64 server which also host
> the database, you get the report render slow.
> You may run the following SQL Statement for the report server database.
> select CAST(C.Name AS VARCHAR(20)) [ReportName],
> E.TimeDataRetrieval,
> E.TimeProcessing,
> E.TimeRendering,
> E.ByteCount,
> E.[RowCount]
> from executionlog E inner join catalog C
> on E.ReportID = C.ItemID
>
> Please let me know the result so that I can provide further assistance.
>
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Chris,
Have you consider to put the credential into the Session?
In ASP.NET, you could put anything you want to the Session.
Also, for more assistance, you may consider to check with
microsoft.public.dotnet.framework.aspnet newsgroup.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Thanks for your response. I am not sure that we could get the information we
need from the aspnet newsgroup - I think this is more a Reporting Services
specific issue.
I agree that it would make sense to cache the credentials in the Session, if
that was possible to do. My next question is can we get access to the
ASP.NET session for our website from within the SSRS forms-authentication
custom security extension that is running in the context of the SSRS
ReportServer webservice? If so, can you provide a detailed code example of
how to do this.
Thanks, in anticipation.
Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:AoHoHnoIIHA.4200@.TK2MSFTNGHUB02.phx.gbl...
> Hello Chris,
> Have you consider to put the credential into the Session?
> In ASP.NET, you could put anything you want to the Session.
> Also, for more assistance, you may consider to check with
> microsoft.public.dotnet.framework.aspnet newsgroup.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Chris,
I did not have such a code example yet.
Since this issue may related with Form Authentication and Asp.net session,
ASP.NET newsgroup will have more expert on this issue.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
We are running Reporting Services 2005 32-bit edition on a Windows 2003 R2
x64 platform, and SQL Server 2005 64-bit database server on Windows 2003 R2
x64. The Reporting Services catalog and the reporting datasource database
are both hosted remotely on the database server.
We are using the Reporting Services ReportViewer web-forms control version
8.0.50727.817 in our ASP.NET 2.0 32-bit web application. We are using custom
forms-authentication.
We have tested this same setup all hosted on a single machine, and it
flies - even with complex reports we can render a report in a couple of
seconds, so we are confident that our report queries and the database itself
are not an issue.
HOWEVER, as soon as we move the RS catalog database to the remote database
server (where it needs to be in our production environment), performance
becomes unacceptably slow - reports that renedered in a couple of seconds on
a single machine typically take 10 to 20 times longer to render.
Please, any advice on what could be causing this problem will be gratefully
received. We have explored things including network addressing, and ensured
that our database server name (which Reporting Services is configured to use
to talk to the database server) has an entry in the internal DNS.
Chris LewisHello Chris,
I understand that when you run the report on a x64 server which also host
the database, you get the report render slow.
You may run the following SQL Statement for the report server database.
select CAST(C.Name AS VARCHAR(20)) [ReportName],
E.TimeDataRetrieval,
E.TimeProcessing,
E.TimeRendering,
E.ByteCount,
E.[RowCount]
from executionlog E inner join catalog C
on E.ReportID = C.ItemID
Please let me know the result so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
We have been doing further testing, and we are 90% sure that custom (forms)
authentication and report access checking is at the root of the problem - I
think the local/remote catalog issue that I originally raised was a
red-herring, so please ignore this. We ran the query you supplied, and the
results showed that our database and the queries were performing well - no
problems with getting data for reports.
So, here is where we believe the problems lie...We have an Internet-facing
ASP.NET application that uses Reporting Services, and we have over 5000
individual users on our SQL Server database - each is registered with a
unique user ID on Reporting Services. When our custom authentication
components are called by SSRS to check access to reports, resources, etc.
for the logged-in user the access check is taking a long time to execute,
and we think we know why - here is a snippet of the code (taken from the
SSRS Forms authentication sample code, which we are using to a large extent)
in Authorization.cs which is a class within our custom authentication
extension component that implements IAuthorizationExtension. The key issue
is the foreach(AceStruct ace in acl) loop, which iterates once for each SSRS
user - and with 5000+ users (and growing rapidly) this is impacting
performance in a major way:
public bool CheckAccess(
string userName,
IntPtr userToken,
byte[] secDesc,
CatalogOperation requiredOperation)
{
AceCollection acl = DeserializeAcl(secDesc);
foreach(AceStruct ace in acl)
{
// First check to see if the user or group has an access control
// entry for the item
if (0 == String.Compare(userName, ace.PrincipalName, true,
CultureInfo.CurrentCulture))
{
// If an entry is found,
// return true if the given required operation
// is contained in the ACE structure
foreach(CatalogOperation aclOperation in ace.CatalogOperations)
{
if (aclOperation == requiredOperation)
return true;
}
}
}
return false;
}
My question is, how can we do this better and more efficiently? These access
checks are performed multiple times for each report request, so one question
I have is is there a way we can cache the results of a CheckAccess call for
a user, so we don't have to iterate through all the users again for that
user's session?
We need a solution that will scale for unlimited users.
Thanks for any advice.
Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:MvBzX9OIIHA.4268@.TK2MSFTNGHUB02.phx.gbl...
> Hello Chris,
> I understand that when you run the report on a x64 server which also host
> the database, you get the report render slow.
> You may run the following SQL Statement for the report server database.
> select CAST(C.Name AS VARCHAR(20)) [ReportName],
> E.TimeDataRetrieval,
> E.TimeProcessing,
> E.TimeRendering,
> E.ByteCount,
> E.[RowCount]
> from executionlog E inner join catalog C
> on E.ReportID = C.ItemID
>
> Please let me know the result so that I can provide further assistance.
>
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi,
(Sorry for repeat post)
We have been doing further testing, and we are 90% sure that custom (forms)
authentication and report access checking is at the root of the problem - I
think the local/remote catalog issue that I originally raised was a
red-herring, so please ignore this. We ran the query you supplied, and the
results showed that our database and the queries were performing well - no
problems with getting data for reports.
So, here is where we believe the problems lie...We have an Internet-facing
ASP.NET application that uses Reporting Services, and we have over 5000
individual users on our SQL Server database - each is registered with a
unique user ID on Reporting Services. When our custom authentication
components are called by SSRS to check access to reports, resources, etc.
for the logged-in user the access check is taking a long time to execute,
and we think we know why - here is a snippet of the code (taken from the
SSRS Forms authentication sample code, which we are using to a large extent)
in Authorization.cs which is a class within our custom authentication
extension component that implements IAuthorizationExtension. The key issue
is the foreach(AceStruct ace in acl) loop, which iterates once for each SSRS
user - and with 5000+ users (and growing rapidly) this is impacting
performance in a major way:
public bool CheckAccess(
string userName,
IntPtr userToken,
byte[] secDesc,
CatalogOperation requiredOperation)
{
AceCollection acl = DeserializeAcl(secDesc);
foreach(AceStruct ace in acl)
{
// First check to see if the user or group has an access control
// entry for the item
if (0 == String.Compare(userName, ace.PrincipalName, true,
CultureInfo.CurrentCulture))
{
// If an entry is found,
// return true if the given required operation
// is contained in the ACE structure
foreach(CatalogOperation aclOperation in ace.CatalogOperations)
{
if (aclOperation == requiredOperation)
return true;
}
}
}
return false;
}
My question is, how can we do this better and more efficiently? These access
checks are performed multiple times for each report request, so one question
I have is is there a way we can cache the results of a CheckAccess call for
a user, so we don't have to iterate through all the users again for that
user's session?
We need a solution that will scale for unlimited users.
Thanks for any advice.
Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:MvBzX9OIIHA.4268@.TK2MSFTNGHUB02.phx.gbl...
> Hello Chris,
> I understand that when you run the report on a x64 server which also host
> the database, you get the report render slow.
> You may run the following SQL Statement for the report server database.
> select CAST(C.Name AS VARCHAR(20)) [ReportName],
> E.TimeDataRetrieval,
> E.TimeProcessing,
> E.TimeRendering,
> E.ByteCount,
> E.[RowCount]
> from executionlog E inner join catalog C
> on E.ReportID = C.ItemID
>
> Please let me know the result so that I can provide further assistance.
>
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Chris,
Have you consider to put the credential into the Session?
In ASP.NET, you could put anything you want to the Session.
Also, for more assistance, you may consider to check with
microsoft.public.dotnet.framework.aspnet newsgroup.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Thanks for your response. I am not sure that we could get the information we
need from the aspnet newsgroup - I think this is more a Reporting Services
specific issue.
I agree that it would make sense to cache the credentials in the Session, if
that was possible to do. My next question is can we get access to the
ASP.NET session for our website from within the SSRS forms-authentication
custom security extension that is running in the context of the SSRS
ReportServer webservice? If so, can you provide a detailed code example of
how to do this.
Thanks, in anticipation.
Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:AoHoHnoIIHA.4200@.TK2MSFTNGHUB02.phx.gbl...
> Hello Chris,
> Have you consider to put the credential into the Session?
> In ASP.NET, you could put anything you want to the Session.
> Also, for more assistance, you may consider to check with
> microsoft.public.dotnet.framework.aspnet newsgroup.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Chris,
I did not have such a code example yet.
Since this issue may related with Form Authentication and Asp.net session,
ASP.NET newsgroup will have more expert on this issue.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Sunday, February 19, 2012
Extracting RDL file from published reports in RS 2000
In order to have a backup of the rdl files for reporting, does exist a manner
to extract the rdl files inside RS starting from published reports?
In other terms, is it possible to revert the report publishing process in
order to have the rdl files?
Many thanksOn Sep 11, 3:44 am, Pasquale <Pasqu...@.discussions.microsoft.com>
wrote:
> In order to have a backup of the rdl files for reporting, does exist a manner
> to extract the rdl files inside RS starting from published reports?
> In other terms, is it possible to revert the report publishing process in
> order to have the rdl files?
> Many thanks
Try this application, works great for me. http://sqldbatips.com/showarticle.asp?ID=62|||Oh, well! It's very useful.
Many thanks
"Tim Toennies" wrote:
> On Sep 11, 3:44 am, Pasquale <Pasqu...@.discussions.microsoft.com>
> wrote:
> > In order to have a backup of the rdl files for reporting, does exist a manner
> > to extract the rdl files inside RS starting from published reports?
> > In other terms, is it possible to revert the report publishing process in
> > order to have the rdl files?
> > Many thanks
> Try this application, works great for me. http://sqldbatips.com/showarticle.asp?ID=62
>
to extract the rdl files inside RS starting from published reports?
In other terms, is it possible to revert the report publishing process in
order to have the rdl files?
Many thanksOn Sep 11, 3:44 am, Pasquale <Pasqu...@.discussions.microsoft.com>
wrote:
> In order to have a backup of the rdl files for reporting, does exist a manner
> to extract the rdl files inside RS starting from published reports?
> In other terms, is it possible to revert the report publishing process in
> order to have the rdl files?
> Many thanks
Try this application, works great for me. http://sqldbatips.com/showarticle.asp?ID=62|||Oh, well! It's very useful.
Many thanks
"Tim Toennies" wrote:
> On Sep 11, 3:44 am, Pasquale <Pasqu...@.discussions.microsoft.com>
> wrote:
> > In order to have a backup of the rdl files for reporting, does exist a manner
> > to extract the rdl files inside RS starting from published reports?
> > In other terms, is it possible to revert the report publishing process in
> > order to have the rdl files?
> > Many thanks
> Try this application, works great for me. http://sqldbatips.com/showarticle.asp?ID=62
>
Extracting month, day, year from dates
I need help in building a Select statement for an ODBC connection to an AS400 DB2 system. I am using this Select statement for SQL Reporting Services.
Unfortunately 15 years ago one of the main datatables on the AS400 was set up splitting the transaction date into THREE FIELDS! One for Month, one for Day and then one for Year.
I need to take yesterday's date and somehow use this in my WHERE statement to connect the transaction records that are in this AS400 table. This is what I have now that is not working:
WHERE QS36F.BRDMST."BREED#" = QS36F.DOGOWNR.BRDCOD AND (QS36F.DOGOWNR.TMONTH = "MONTH"(GETDATE() ) AND
(QS36F.DOGOWNR.TDAY = "DAY"(GETDATE()-1 ) AND (QS36F.DOGOWNR.TYEAR = "YEAR"(GETDATE())
GROUP BY QS36F.BRDMST.BRDESC
How do I extract each day, month, and year from the current date?
Thanks for the information
SqlServer has a DatePart function. Is that what you're looking for?
Mike
|||Mike,
I did try the DATEPART function and was getting an AS400 ODBC Client Express error. I do believe I solved the problem, however I just had this query running over two hours and just killed it. Here is the SELECT statement:
SELECT COUNT(QS36F.BRDMST.BRDESC) AS BreedCount, QS36F.BRDMST.BRDESC
FROM QS36F.BRDMST, QS36F.DOGOWNR
WHERE QS36F.BRDMST."BREED#" = QS36F.DOGOWNR.BRDCOD AND (QS36F.DOGOWNR.TMONTH = "MONTH"(NOW())) AND
(QS36F.DOGOWNR.TDAY = "DAY"(NOW()) - 1) AND (QS36F.DOGOWNR.TYEAR = "YEAR"(NOW()))
GROUP BY QS36F.BRDMST.BRDESC
The DOGOWNR table has about 1,500,000 records.
I need this to be a lot quicker. Might you have any suggestions? My thought is to use SSIS to extract all of the data into SQL from the AS400 and then build the reports through that, except that SSIS will have to run nightly to copy over any differential data.
Thanks for the information.
|||Wish I could help, but I'm no DBA. I wonder where the bottlenecks are, guessing it's the ODBC business and not the query? Would be interesting to run that query right on the AS400 and see how fast it is.
I did a quick google trying to see if there's a .NET driver available. I found this - http://cc.borland.com/Item.aspx?id=23616 - not sure if it will help or not.
Mike
|||Mike,
Our AS400 contractor happens to be here today and I asked her. She said that for the AS400 itself to query this datatable it takes about 1/2 an hour. I do believe too it has something to do with ODBC which is why I might go the SSIS route and replicate the data that is needed.
I will also check out that .Net driver. Thanks for the help.
|||You are going to have a problem with that if it is the 1st of the month or of the year. Make sure you are using Now() or Now() -1 in all instances, not a combination of the two.If your query is running much longer than expected you might want to make sure the data types match up. If your TYear field and Year(Now()) are different data types it is possible that your TYear is being converted rather than the result of Year(Now()). If this was the case I don't think your index would be used. In any case check your query plan to be sure.
When you check the plan, make sure that the expected table is driving the query. I am not familiar with AS400 but in some database implementation this is critical.
count(*) is often more effecient than count(column). Since you are grouping on the column being counted you don't really need count to filter out counting of the null columns.
You might also experiment with a view that does the counting for you, then join the view back tot he breed table.
Create View OwerCountByBreed As
Select brdcod, count(*) "TheCount"
From dogownr
Where
TYear = Year(Now()-1) And
TMonth = Month(Now()-1) And
TDay = Day(Now-1)
Group By brdcod
Select brddesc, TheCount
From mrdmst, ownercountbybreed
Where brdmst.breed# = ownercountbybreed.brdcod
Just some thoughts. Sorry if this was all obvious.
|||
Some of that was obvious but not thought of by me so thank you for the reminders (like having be the first day of the month). I think maybe an easier solution may be to do a DATEADD. I don't know, I will figure it out today.
I also am going to try using the IBM AS400 OleDB adapter as opposed to using ODBC and see if that helps any.
I am mainly a developer, not a DB Admin. I am very familiar with simple SELECT statements. I am learning more and more about advanced SELECT statements.
|||Hi,
I have some experience in AS400/DB2 - it look to me like you are running this on system 36 flat files (system 36 environment on the AS400) am I correct?
This may be a mute point - but have you checked the indexing on the DB2 database? Adding one index may well speed things up.
Also, what anti-virus do you have on your PC? Strange question? I have know Norton realtime scan to try and check all data as it was returned via ODBC - just a thought.
|||I was able to use SSIS useing Derived Colums to pull the data into a Flat File. I then copied the flat file into SQL. The initial pull from the AS400 took about two hours for almost 2.5 million records, but the flat file to SQL took about 30 seconds.
Subscribe to:
Posts (Atom)