I am having a problem implementing constrained delegation for SSRS. I have followed the (very good) instructions located here:
http://sqlblogcasts.com/blogs/stevechowles/archive/2007/06/08/reporting-services-2005-for-the-dba-iis-security.aspx
I have chosen the option of running the application pool for SSRS under a domain user account. This is the same account that I use to run the SSRS service.
I have the authentication providers for the site set to "Negotiate,NTLM".
I also made sure that the application pool user account has rights on the ReportManager and ReportServer directories.
If browse to the URL while logged on to the SSRS server then I am able to access the site
My problem is when I try to access the site from anywhere but locally on the SSRS server:
I get a logon prompt if I try to access the SSRS URL from a different workstation. After three tries to login I get: "You are not authorized to view this page". Even with an account that is local admin on the SSRS Server.
If I set the authentication providers for the site to "NTLM" then I am able to access the site from a different workstation but of couse constrained delegation does not work.
Have i overlooked something? What could be causing the login prompt?
I get this error when, i try to set a parameter for user input, it doesnt matter what i type in, it says this:
<<Query (1,408) The Restrictions imposed by the constrained flag in the STRTOSET function were violated>>
what exactly does this mean? and how do i fix it?
My parameter is for dates, but i have the datetype set to "String", when i change it to" DATETIME" , i get the same error when i select from the calendar. So far the only date option that is working right now. Is a long drop down list, that could go for days at a time. I just want users to be able to input dates, such as "Jan 9, 2000" or 10/10/1999. What can i do about this?
I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?
Hi, Can anyone help me? when I am inserting a record to a dataset I am getting Column 'FieldID' is constrained to be unique. Value '0' is already present. I have 2 fields in table like FieldID,FieldName where FieldID id is Primary key and identity column I am not inserting any value to FieldID as it is identity column. DataRow newDR= dbDataSet.Tables[strTable].NewRow();for (int i = 0; i < e.Row.Cells.Count; i++) { if ( e.Row.Cells[i].Key != "FieldID" ) {if (e.Row.Cells[i].Value != null) { newDR[e.Row.Cells[i].Key] = e.Row.Cells[i].Value;dbDataSet.Tables[ strTable].Rows.Add(newDR); } } Some times it is woking fine with out any error............
I have a question, but first I need to give you some background:
My network works with Active Directory on Windows 2000, and I have web servers running on windows 2003 and SQL Servers 2000 running on Windows 2003.
I wanted to enable account delegation and I found a bunch of information.
Everything seemed "easy", but I tried to test it first on my test servers anyways and this is what happened:
We created the SPN for the SQL Server Account is trusted for delegation check box was selected for the service account of SQL Server. Account is sensitive and cannot be delegated check box was not selected for the user requesting delegation. But when we checked the box Computer is trusted for delegation (and only this box !!) in the server running an instance of SQL Server 2000, the role of this server changed magically (just like this guys, it was magic) from "server" to "Domain Controller".
We were intrigued about this change, but we "trusted" the white paper that we had in front of us.
http://support.microsoft.com/kb/319723
After some hours, the production web servers (of the whole network) and many workstations stopped working:
The IIS on this web servers will show an empty list of websites The network and dial-up connections were missing on the web servers and also on the workstations. The web servers and the workstations affected were "isolated" from the network, the command ping was not finding any of this computers.
Anyway, it was a nightmare, it took a while to fix the mess, we reverted the changes in Active Directory, and this makes me thing that the magical "promotion" of the SQL server to Domain Controller had to do with all this.
the questions is:
Do you have an idea about what could have caused all this? I mean, I still need to enable this account delegation thing. But I would like to know first if someone has done it before in a similar environment or if someone has run into one of the problems described before.
I am trying to implement a linked server that uses integrated authentication on a 64 bit Wndows 2003 SP1 server. I have both Sql Server 2005 and Sql Server installed, and have successfully created database link that is able to use double hop authentiction on the Sql Server 2005 instance. I am unable to do the same usign the Sql Server 2000 instance. Does anyone know if double hop uathentication using Kerberos is supported on Sql Server 2000. The linked server on Sql Server 2005 is created using this syntax
SPN's and domain accounts have been created as documented and those same accounts are used in both the Sql Server 2005 and Sql Server 2000 instances.
The error message going from a Sql Server 2000 or 2005 client, to the Sql Server 2000 instance that has the linked server using the SQLNCLI provider is
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLNCLI' reported an error. Authentication failed. [OLE/DB provider returned message: Communication link failure] [OLE/DB provider returned message: Named Pipes Provider: No process is on the other end of the pipe. ] [OLE/DB provider returned message: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.] OLE DB error trace [OLE/DB Provider 'SQLNCLI' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
If I use the Sql Server 2000 OLEDB provider when creating the link I get this error
Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
The same link using the SQLNCLI provider in Sql Server 2005
works and I am able to use double hop authentication.
My question is , does anyone know if double hop authentication is supported using a Sql Server 2000 linked server?
I have configured Kerberos delegation for several web services. One of the web service calls SSIS packages, but the packages don't run with the expected impersonate user : the package starts with the imporsonate user, but continue with ASPNET user (which is not allowed to execute SSIS and connect to DB).
If the web service is called directly (no delegation), SSIS packages run with the correct user. It looks like than there is an autenthicate issue, but kerberos is configured and web services can run from one to another with the impersonate user. The issue occured only when I call SSIS packages.
Here is a extract of the SSIS log file :
Code Snippet <dtslog> <record> <event>PackageStart</event> <message>Beginning of package execution. </message> <computer>WKS-GE-BRAZILIA</computer> <operator>WKS-GE-BRAZILIAPascal.Brun</operator> <source>ImportMonthlyCSV</source> <sourceid>{D053CB99-FDE4-492D-83BC-821E1B34704B}</sourceid> <executionid>{EA9C1929-4131-4FDD-A6FC-560E01A65536}</executionid> <starttime>09.08.2007 17:31:02</starttime> <endtime>09.08.2007 17:31:02</endtime> <datacode>0</datacode> <databytes>0x</databytes> </record> <record> <event>OnError</event> <message>SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Data Warehouse" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. </message> <computer>WKS-GE-BRAZILIA</computer> <operator>WKS-GE-BRAZILIAASPNET</operator> <source>Import CSV</source> <sourceid>{284D3166-F372-4B03-86C1-75A4D8DC9A5C}</sourceid> <executionid>{EA9C1929-4131-4FDD-A6FC-560E01A65536}</executionid> <starttime>09.08.2007 17:31:02</starttime> <endtime>09.08.2007 17:31:02</endtime> <datacode>-1071611876</datacode> <databytes>0x</databytes> </record> ...
I'm having trouble trying to access a network share that comes via a UNIX server running SAMBA. In the first case, I'm running on my local workstation (A), connected to a remote server (B), and attempting to access directory information for a path like:
\a0amsimmsworkseaborg argets11as2981
This path is fully accessible by me from the workstation (A) and the server (B). The files and directories below "work" in the above path are also wide open on the UNIX side (meaning r-xr-xrwx permissions). However, if I attempt to do something like this:
at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)
at System.IO.Directory.GetFiles(String path)
at StoredProcedures.mdcinfo(Int32 sim_id, String mdc_base)
CWD is: C:WINDOWSsystem32
User is: amsimms
Initial is: dbserver
Interestingly, if I run the procedure directly on the server (B), I do not get the exception. So this seems to be more of a delegation problem. The server B's sql server instance is running as a domain account (dbserver), which has been enabled for delegation and an spn has been set up. Is there something beyond this either with the impersonate or delegation configuration that I need to do in order for this to work?
I've just installed SQL 2005 SP2 Rollup 3 Package (Build 3186) on a 2 node X64 W2K3 Cluster. Everything went fine, although after the install, the SQLAgent Services of my instances started to complain about delegation not enabled for the domain account used for the SQLAgent Service.
SPN's were already registered, so I've enabled unconstrained delegation & no errors anymore..
Apparently we're obliged to enable delegation as soon as this hotfix is installed
(maybe due to fix 938086 included in it ?)
To make this setup more secure, we would like to enable constrained delegation. This does not seem to work, as soon as we choose constrained delegation by adding the SPN of the clustername to the domain user account we're running with & restart the sql agent, it fails with the same error as when no delegation was configured:
! [298] SQLServer Error: 22022, CryptUnprotectData() returned error -2146892987, 'The requested operation cannot be completed. The computer must be trusted for delegation and the current user account must be configured to allow delegation.' [SQLSTATE 42000] ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
Summary: With Unconstrained delegation enabled for computer account & user account, everything goes fine. As soon as constrained delegation is chosen, by adding the SPN's to the domain user account of SQL Agent, SQL agent fails to start.
We use a domain account for the SQL Agent. This account has an 2 SPN registered on it
I am trying to create an asp.net website with integrated windows authentication to access SQL databases. IIS resides on WinXP and SQL Server on Win2000 SRV. Both are in the same NT Domain. IIS and SQL Server cannot reside on the same machine and a stand alone web server is ideal as the website needs to access multiple SQL Servers. IIS is set to Integrated Windows Authentication. The machine running IIS & the SQL Server are set to be "trusted for delegation" in active directory. The domain user accounts that will be accessing the databases are not marked as "Account is sensitive and cannot be delegated".
The connection string that the web app uses to connect to SQL database is:
with which the user credentials should be flown to the SQL database. But instead the delegation fails and results in the following ANONYMOUS authentication failure error:
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) _______________ Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 ---------------------
I'm using MS Report Designer 2005 and have created a report that uses a cube, with a dimension set up to convert null values to unknown (nullProcessing = UnknownMember).
When I create a parameter using the checkbox in the graphical design mode's filter pane, Report Designer automatically sets the constrained flag, eg:
When running the report and selecting the 'Unkown' value from the parameter list, the error 'the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated' occurrs.
How can I prevent the constrained flag from being used, or am I doing something wrong with converting null values to 'Unknown'?
Does anyone know of a link or list that has all the parameters for the "rs:" section of the URL access parameter, except for the ones in the Microsoft books?
Is it possible to write SSRS 2008 reports to run on an existing SSRS 2005 server?
If yes, what do I need to do to be able to write SSRS 2008 reports on my developer PC that will run on the SQL 2005 Server (which also has SSRS 2005 installed on it)?
I installed Visual Studio 2008 beta on the development PC and it appears that it needs SSRS installed on it too. So I installed SQL 2008 SSRS (SQL Server Reporting Services) and SQL 2008 Workstation Components onto the developer PC. Next I run the SQL 2008 "Reporting Services Configuration" tool. When I click on the "Web Service URL" section it hangs indefinitely and I have to force it closed.
The developer PC is Windows Vista Business.
Any suggestions on making this work, or any other information that you can think of that would allow me to use Visual Studio 2008 and the SQL 2008 "Report Designer Preview" tool to develop SSRS reports that will run on the SQL 2005 SSRS server?
Hi, In a .net application there is a link that brings up a SSRS report. I have noticed that if it is the first time this report is requested i.e. Application has just been opened and the report button is clicked, then it takes a while to get this report to appear on the screen. But if this report is requested again (i.e. for the second time or more) then it only takes a few moments for the report to appear on the screen. So it seems that only the first time the report is requested it takes a longer time to get this report. Is there a way to reduce this initial load of the report? Thanks
Can anybody give share with me how to establish the basic connection between SQL Reporting Services and Great Plains 9.0? They are on two separate servers if that makes a difference.
Hi. We currently have about a 100 Crystal Reports with embedded images and text in the *.rpt files. If we were to switch to SSRS 2005,
Can we do a global search-and-replace of images and text within the SSRS reports? We have some letterheads that have names of people who might change every year or two.
Are there any tools to convert Crystal Reports *.rpt files to SSRS reports? These 100 reports would be time-consuming trying to convert to SSRS. Thank you much Alex
I have an ancient app that produces a pipe delimited file for the printing of certain invoices (which i can present in reporting services). I am able to fire a batch file or script from this able and pass the name of the file through to the script...
My question is: is it possible to have the stored procedure fire a DTS pkg to "massage the data" before running the report?
Has anyone had any experience doing this or other ideas i could try. i'm using SQL2k
i want to display a report in SSRS in two groups 1st group (001, 021, 003, 005) there is no 022 in this table where in another group (006,036) there is no 002. it should display as follow with the total of sales
I am going to Intsall SSRS on Cluster load balanced web servers. Shall i install SSRS on both nodes? When i go to msdn, the caption as:
"You must use other products to support access through a virtual server and cluster deployment. Reporting Services does not provide load balancing functionality or virtual server features that allow you to access all report server instances through a shared URL address."
I have my vip and virtual url ready for installation. how can i install SSRS in high availability? please advice. Thanks.
I am fairly new to SSRS. I want to install SSRS on my local system which has OS of Win XP sp2 (not a server). I have installed SQL Server instance on this box. Can any body tell if i need Win Server 2000 or 2003 OS to install SSRS or can i use Win XP, if so please give some guidelines.
Hello everybody, I'm new to SQL,I have a interview tomor,so please i need a website from where i can get questions related to SSIS & SSRS.Please help me. Thanks in advance
I'm trying to set up the Report Manager Virtual Directory. But the little X just wont turn into a tick - even though I click New, then OK, then Apply.
Has anyone else had this problem?
I've managed to get SSRS running before but for the life of me I can't get it going! So annoying! Does anyone have a link to a simple Walkthrough or something for getting SSRS of the ground so that when you navigate to http://localhost/reportserver, you don't get "The Page can not be found".
Hi All, I have a project in MS Acces, I have to do the entire project in .Net.. The same MS Access Project has a lot of reports and For these reports I have to use SSRS..So, I need to know about importing the Access reports to SSRS.. Is this advisable to import the reports from MS Acces to SSRS or to create new reports in SSRS..If I import the MS Access reports to the SSRS ..can I use them as the same as that of the newly created reports in SSRS.. As I imported all the reports but failed to understand how the query is written...and how the data is drawn from the datasource...As I dont have nay data in my SQL Server 2005.. How the reports are showing the data..in SSRS when they are imported from ACCESS..as my SQL Server has no data in it...Please help me it is very urgent..I am new to the reporting ..so need more information where I can Learn more..
My Maijn question is that is this Advisable...to import the Reports from MS ACCESS to SSRS or build the new reports..If imported what are the measures I have to take...to look and feel the reports as that are made freshly in SSRS...The reports are really big ones in MS ACCESS.. Replies needed ASAP..
