|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL2006 CLR calling webserviceI am trying to call the SSRS webservice from within SQL. Wrote the original code on a client m/c as a command-line app and it works just fine. Change the code to be a CLR Function and get : Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. When calling execInfo = rs.LoadReport(reportpath, historyID); NB I had to change the 'DefaultCredentials' to even get this far, to use a known user on the server. Server is running on Server 2003 - so I guess its probably a lockdown security issue ???. Seems like SQL doesn't have enough rights to access the SSRS webservice ???. At a loss though to know how I give it permissions :-((,. Thanks Regards Graham relevant code snippet : ReportExecutionService rs = new ReportExecutionService(); NetworkCredential cr = new NetworkCredential("uuuuuuu", "pppppppp"); rs.Credentials = cr; ExecutionInfo execInfo = new ExecutionInfo(); ExecutionHeader execHeader = new ExecutionHeader(); try { rs.ExecutionHeaderValue = execHeader; execInfo = rs.LoadReport(reportpath, historyID); ..... Oops - sorry - getting ahead of myself with SQL2006 :-O.
Graham Show quoteHide quote "Oxns" <oxns@community.nospam> wrote in message news:ONpaSMgiGHA.4304@TK2MSFTNGP03.phx.gbl... > Hi, > > I am trying to call the SSRS webservice from within SQL. > > Wrote the original code on a client m/c as a command-line app and it works > just fine. Change the code to be a CLR Function and get : > > Request for the permission of type 'System.Net.WebPermission, System, > Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. > > When calling > execInfo = rs.LoadReport(reportpath, historyID); > > NB I had to change the 'DefaultCredentials' to even get this far, to use a > known user on the server. > > Server is running on Server 2003 - so I guess its probably a lockdown > security issue ???. Seems like SQL doesn't have enough rights to access > the SSRS webservice ???. > > > > At a loss though to know how I give it permissions :-((,. > > > > Thanks > > > > Regards > > > > Graham > > relevant code snippet : > > ReportExecutionService rs = new ReportExecutionService(); > > NetworkCredential cr = new NetworkCredential("uuuuuuu", "pppppppp"); > > rs.Credentials = cr; > > ExecutionInfo execInfo = new ExecutionInfo(); > > ExecutionHeader execHeader = new ExecutionHeader(); > > try > > { > > > > rs.ExecutionHeaderValue = execHeader; > > execInfo = rs.LoadReport(reportpath, historyID); > > .... > > > > Hello,
Did you try EXTERNAL_ACCESS and UNSAFE security for the assembly? http://msdn2.microsoft.com/en-us/library/ms189524.aspx Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner 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. Peter,
Thanks for the response - tried this (once I figured where to make the change - in my post build deploy script. Gave the following errors though :-(. Executing deploy script: PostDeployScript.sql ... Error: executing deploy script failed. CREATE ASSEMBLY for assembly 'SqlReportHandler.XmlSerializers' failed because assembly 'SqlReportHandler.XmlSerializers' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.: PostDeployScript.sql Executing deploy script: PostDeployScript.sql ... Error: executing deploy script failed. CREATE ASSEMBLY for assembly 'SqlReportHandler.XmlSerializers' failed because assembly 'SqlReportHandler.XmlSerializers' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.: PostDeployScript.sql Graham ""privatenews"" <pet***@online.microsoft.com> wrote in message Show quoteHide quote news:3WJk%23UhiGHA.5184@TK2MSFTNGXA01.phx.gbl... > Hello, > > Did you try EXTERNAL_ACCESS and UNSAFE security for the assembly? > > http://msdn2.microsoft.com/en-us/library/ms189524.aspx > > Best Regards, > > Peter Yang > MCSE2000/2003, MCSA, MCDBA > Microsoft Online Partner 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 Oxns,
SQLCLR can do webservices (at permission_set = external_access) but SQL Server doesn't allow dynamic serializers, like the "add web reference" mechanism eventually produces. You need to manually create your serializer with sgen and catalog it to SQL Server (create assembly) separately. Our new book shows how to do this in two places; one is in chapter 4, which happens to be in the sample chapter at http://www.SQLskills.com under "Books". It's easier than explaining the whole process here (because there is an additional consideration to make it run as external_access); write back if you have additional questions. Cheers, Bob Beauchemin http://www.SQLskills.com/blogs/bobb Show quoteHide quote "Oxns" <oxns@community.nospam> wrote in message news:OO$NZZiiGHA.1276@TK2MSFTNGP03.phx.gbl... > Peter, > > Thanks for the response - tried this (once I figured where to make the > change - in my post build deploy script. > > Gave the following errors though :-(. > > Executing deploy script: PostDeployScript.sql ... > > Error: executing deploy script failed. CREATE ASSEMBLY for assembly > 'SqlReportHandler.XmlSerializers' failed because assembly > 'SqlReportHandler.XmlSerializers' is not authorized for PERMISSION_SET = > UNSAFE. The assembly is authorized when either of the following is true: > the database owner (DBO) has UNSAFE ASSEMBLY permission and the database > has the TRUSTWORTHY database property on; or the assembly is signed with a > certificate or an asymmetric key that has a corresponding login with > UNSAFE ASSEMBLY permission.: PostDeployScript.sql > > > > Executing deploy script: PostDeployScript.sql ... > > Error: executing deploy script failed. CREATE ASSEMBLY for assembly > 'SqlReportHandler.XmlSerializers' failed because assembly > 'SqlReportHandler.XmlSerializers' is not authorized for PERMISSION_SET = > EXTERNAL_ACCESS. The assembly is authorized when either of the following > is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission > and the database has the TRUSTWORTHY database property on; or the assembly > is signed with a certificate or an asymmetric key that has a corresponding > login with EXTERNAL ACCESS ASSEMBLY permission.: PostDeployScript.sql > > Graham > > > > ""privatenews"" <pet***@online.microsoft.com> wrote in message > news:3WJk%23UhiGHA.5184@TK2MSFTNGXA01.phx.gbl... >> Hello, >> >> Did you try EXTERNAL_ACCESS and UNSAFE security for the assembly? >> >> http://msdn2.microsoft.com/en-us/library/ms189524.aspx >> >> Best Regards, >> >> Peter Yang >> MCSE2000/2003, MCSA, MCDBA >> Microsoft Online Partner 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. >> >> > > Bob,
Thanks for the input - took a look at the book chapter and it had some useful stuff ;-)). I had already done much of the work serializer, sql scripts etc from other examples. The one thing I did add was your Impersonate code, which I was not using. This changed the fault but it still doeasn't run :-((. I am not (directly) doing SQLClient data access so had thought that a Function could be used - it now however tells me not. So I put the same code (almost) into a Stored Proc, but that tells me that it aborts Msg 10312, Level 16, State 49, Procedure StoredProcedure1, Line 0 ..NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token. This is now doing my head in :-(( - this error is all but meaningless (to me anyway). I seem to be going through hoops here to do what is seemingly a simple task :-O. NB I have also secured the dll with certificates - as there was some thought that this was necessary for 'EXTERNAL_ACCESS'. I am rapidly coming to the conclusion that this is more trouble than its worth and that there must be a far better way of generating a report from within an SP. Thanks for your help. In case you have further interest - I have copied my code below : Many Thanks Graham using System; using System.IO; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using SqlReportHandler.ReportExecutionService; using System.Web.Services.Protocols; using System.Net; using System.Security.Principal; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void StoredProcedure1(string format, string filename, string reportpath) { string logme = "Start, "; byte[] result = null; string historyID = null; string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"; string encoding; string mimeType; string extension; Warning[] warnings = null; ParameterValue[] reportHistoryParameters = null; string[] streamIDs = null; using (WindowsIdentity id = SqlContext.WindowsIdentity) { WindowsImpersonationContext c = id.Impersonate(); ReportExecutionService rs = new ReportExecutionService(); rs.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials; ExecutionInfo execInfo = new ExecutionInfo(); ExecutionHeader execHeader = new ExecutionHeader(); logme += "Try1, "; try { // Prepare report parameter. ParameterValue[] parameters = null; /* ParameterValue[] parameters = new ParameterValue[3]; parameters[0] = new ParameterValue(); parameters[0].Name = "EmpID"; parameters[0].Value = "288"; parameters[1] = new ParameterValue(); parameters[1].Name = "ReportMonth"; parameters[1].Value = "6"; // June parameters[2] = new ParameterValue(); parameters[2].Name = "ReportYear"; parameters[2].Value = "2004"; */ rs.ExecutionHeaderValue = execHeader; logme += "Try1.1, "; execInfo = rs.LoadReport(reportpath, historyID); logme += "Try1.2, "; // String SessionId = rs.ExecutionHeaderValue.ExecutionID; } catch (Exception ex) { logme += ex.Message + ", "; } logme += "Try2, "; try { result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs); execInfo = rs.GetExecutionInfo(); } catch (SoapException e) { logme += e.Message + ", "; } logme += "Try3, "; // Write the contents of the report to an xxx file. try { FileStream stream = File.Create(filename, result.Length); stream.Write(result, 0, result.Length); stream.Close(); } catch (Exception e) { logme += e.Message + ", "; } c.Undo(); } } }; Show quoteHide quote "Bob Beauchemin" <bobb_no_spam@SQLskills.com> wrote in message news:OmTUcaliGHA.2456@TK2MSFTNGP04.phx.gbl... > Hi Oxns, > > SQLCLR can do webservices (at permission_set = external_access) but SQL > Server doesn't allow dynamic serializers, like the "add web reference" > mechanism eventually produces. You need to manually create your serializer > with sgen and catalog it to SQL Server (create assembly) separately. Our > new book shows how to do this in two places; one is in chapter 4, which > happens to be in the sample chapter at http://www.SQLskills.com under > "Books". It's easier than explaining the whole process here (because there > is an additional consideration to make it run as external_access); write > back if you have additional questions. > > Cheers, > Bob Beauchemin > http://www.SQLskills.com/blogs/bobb > > > "Oxns" <oxns@community.nospam> wrote in message > news:OO$NZZiiGHA.1276@TK2MSFTNGP03.phx.gbl... >> Peter, >> >> Thanks for the response - tried this (once I figured where to make the >> change - in my post build deploy script. >> >> Gave the following errors though :-(. >> >> Executing deploy script: PostDeployScript.sql ... >> >> Error: executing deploy script failed. CREATE ASSEMBLY for assembly >> 'SqlReportHandler.XmlSerializers' failed because assembly >> 'SqlReportHandler.XmlSerializers' is not authorized for PERMISSION_SET = >> UNSAFE. The assembly is authorized when either of the following is true: >> the database owner (DBO) has UNSAFE ASSEMBLY permission and the database >> has the TRUSTWORTHY database property on; or the assembly is signed with >> a certificate or an asymmetric key that has a corresponding login with >> UNSAFE ASSEMBLY permission.: PostDeployScript.sql >> >> >> >> Executing deploy script: PostDeployScript.sql ... >> >> Error: executing deploy script failed. CREATE ASSEMBLY for assembly >> 'SqlReportHandler.XmlSerializers' failed because assembly >> 'SqlReportHandler.XmlSerializers' is not authorized for PERMISSION_SET = >> EXTERNAL_ACCESS. The assembly is authorized when either of the following >> is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission >> and the database has the TRUSTWORTHY database property on; or the >> assembly is signed with a certificate or an asymmetric key that has a >> corresponding login with EXTERNAL ACCESS ASSEMBLY permission.: >> PostDeployScript.sql >> >> Graham >> >> >> >> ""privatenews"" <pet***@online.microsoft.com> wrote in message >> news:3WJk%23UhiGHA.5184@TK2MSFTNGXA01.phx.gbl... >>> Hello, >>> >>> Did you try EXTERNAL_ACCESS and UNSAFE security for the assembly? >>> >>> http://msdn2.microsoft.com/en-us/library/ms189524.aspx >>> >>> Best Regards, >>> >>> Peter Yang >>> MCSE2000/2003, MCSA, MCDBA >>> Microsoft Online Partner 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. >>> >>> >> >> > > Hmmm... I've seen both of these. ;-)
UDF will work if you specify DataAccess=DataAccessKind.Read in the SqlFunction attribute. Using the identity is "data access". "Could not revert thread token" usually just means your procedure threw an unhandled exception and didn't call Revert. Put a try-catch block in your SQLCLR code to catch the real exception. And/or run it in the debugger. Hope this helps, Bob Beauchemin http://www.SQLskills.com/blogs/bobb Show quoteHide quote "Oxns" <oxns@community.nospam> wrote in message news:eTcm9VmiGHA.4140@TK2MSFTNGP03.phx.gbl... > Bob, > > Thanks for the input - took a look at the book chapter and it had some > useful stuff ;-)). > > I had already done much of the work serializer, sql scripts etc from other > examples. The one thing I did add was your Impersonate code, which I was > not using. This changed the fault but it still doeasn't run :-((. > > I am not (directly) doing SQLClient data access so had thought that a > Function could be used - it now however tells me not. So I put the same > code (almost) into a Stored Proc, but that tells me that it aborts > > Msg 10312, Level 16, State 49, Procedure StoredProcedure1, Line 0 > > .NET Framework execution was aborted. The UDP/UDF/UDT did not revert > thread token. > > > > > This is now doing my head in :-(( - this error is all but meaningless (to > me anyway). I seem to be going through hoops here to do what is seemingly > a simple task :-O. > > NB I have also secured the dll with certificates - as there was some > thought that this was necessary for 'EXTERNAL_ACCESS'. > > I am rapidly coming to the conclusion that this is more trouble than its > worth and that there must be a far better way of generating a report from > within an SP. > > Thanks for your help. In case you have further interest - I have copied my > code below : > > Many Thanks > > Graham > > > using System; > > using System.IO; > > using System.Data; > > using System.Data.SqlClient; > > using System.Data.SqlTypes; > > using Microsoft.SqlServer.Server; > > using SqlReportHandler.ReportExecutionService; > > using System.Web.Services.Protocols; > > using System.Net; > > using System.Security.Principal; > > public partial class StoredProcedures > > { > > [Microsoft.SqlServer.Server.SqlProcedure] > > public static void StoredProcedure1(string format, string filename, string > reportpath) > > { > > string logme = "Start, "; > > byte[] result = null; > > string historyID = null; > > string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"; > > string encoding; > > string mimeType; > > string extension; > > Warning[] warnings = null; > > ParameterValue[] reportHistoryParameters = null; > > string[] streamIDs = null; > > using (WindowsIdentity id = SqlContext.WindowsIdentity) > > { > > WindowsImpersonationContext c = id.Impersonate(); > > ReportExecutionService rs = new ReportExecutionService(); > > rs.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials; > > ExecutionInfo execInfo = new ExecutionInfo(); > > ExecutionHeader execHeader = new ExecutionHeader(); > > logme += "Try1, "; > > try > > { > > // Prepare report parameter. > > ParameterValue[] parameters = null; > > /* ParameterValue[] parameters = new ParameterValue[3]; > > parameters[0] = new ParameterValue(); > > parameters[0].Name = "EmpID"; > > parameters[0].Value = "288"; > > parameters[1] = new ParameterValue(); > > parameters[1].Name = "ReportMonth"; > > parameters[1].Value = "6"; // June > > parameters[2] = new ParameterValue(); > > parameters[2].Name = "ReportYear"; > > parameters[2].Value = "2004"; > > */ > > rs.ExecutionHeaderValue = execHeader; > > logme += "Try1.1, "; > > execInfo = rs.LoadReport(reportpath, historyID); > > logme += "Try1.2, "; > > // String SessionId = rs.ExecutionHeaderValue.ExecutionID; > > } > > catch (Exception ex) > > { > > logme += ex.Message + ", "; > > } > > logme += "Try2, "; > > try > > { > > result = rs.Render(format, devInfo, out extension, out encoding, out > mimeType, out warnings, out streamIDs); > > execInfo = rs.GetExecutionInfo(); > > } > > catch (SoapException e) > > { > > logme += e.Message + ", "; > > } > > logme += "Try3, "; > > // Write the contents of the report to an xxx file. > > try > > { > > FileStream stream = File.Create(filename, result.Length); > > stream.Write(result, 0, result.Length); > > stream.Close(); > > } > > catch (Exception e) > > { > > logme += e.Message + ", "; > > } > > c.Undo(); > > } > > } > > }; > > > "Bob Beauchemin" <bobb_no_spam@SQLskills.com> wrote in message > news:OmTUcaliGHA.2456@TK2MSFTNGP04.phx.gbl... >> Hi Oxns, >> >> SQLCLR can do webservices (at permission_set = external_access) but SQL >> Server doesn't allow dynamic serializers, like the "add web reference" >> mechanism eventually produces. You need to manually create your >> serializer with sgen and catalog it to SQL Server (create assembly) >> separately. Our new book shows how to do this in two places; one is in >> chapter 4, which happens to be in the sample chapter at >> http://www.SQLskills.com under "Books". It's easier than explaining the >> whole process here (because there is an additional consideration to make >> it run as external_access); write back if you have additional questions. >> >> Cheers, >> Bob Beauchemin >> http://www.SQLskills.com/blogs/bobb >> >> >> "Oxns" <oxns@community.nospam> wrote in message >> news:OO$NZZiiGHA.1276@TK2MSFTNGP03.phx.gbl... >>> Peter, >>> >>> Thanks for the response - tried this (once I figured where to make the >>> change - in my post build deploy script. >>> >>> Gave the following errors though :-(. >>> >>> Executing deploy script: PostDeployScript.sql ... >>> >>> Error: executing deploy script failed. CREATE ASSEMBLY for assembly >>> 'SqlReportHandler.XmlSerializers' failed because assembly >>> 'SqlReportHandler.XmlSerializers' is not authorized for PERMISSION_SET = >>> UNSAFE. The assembly is authorized when either of the following is true: >>> the database owner (DBO) has UNSAFE ASSEMBLY permission and the database >>> has the TRUSTWORTHY database property on; or the assembly is signed with >>> a certificate or an asymmetric key that has a corresponding login with >>> UNSAFE ASSEMBLY permission.: PostDeployScript.sql >>> >>> >>> >>> Executing deploy script: PostDeployScript.sql ... >>> >>> Error: executing deploy script failed. CREATE ASSEMBLY for assembly >>> 'SqlReportHandler.XmlSerializers' failed because assembly >>> 'SqlReportHandler.XmlSerializers' is not authorized for PERMISSION_SET = >>> EXTERNAL_ACCESS. The assembly is authorized when either of the following >>> is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY >>> permission and the database has the TRUSTWORTHY database property on; or >>> the assembly is signed with a certificate or an asymmetric key that has >>> a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.: >>> PostDeployScript.sql >>> >>> Graham >>> >>> >>> >>> ""privatenews"" <pet***@online.microsoft.com> wrote in message >>> news:3WJk%23UhiGHA.5184@TK2MSFTNGXA01.phx.gbl... >>>> Hello, >>>> >>>> Did you try EXTERNAL_ACCESS and UNSAFE security for the assembly? >>>> >>>> http://msdn2.microsoft.com/en-us/library/ms189524.aspx >>>> >>>> Best Regards, >>>> >>>> Peter Yang >>>> MCSE2000/2003, MCSA, MCDBA >>>> Microsoft Online Partner 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. >>>> >>>> >>> >>> >> >> > > Bob,
Many thanks for your ongoing support - pity that Microsoft can't jump in and simply advise what security context I should be using :-((. I am now beginning to understand the issues ; 1. Don't really want to Impersonate the current Windows user as the database will probably be accessed by SQL logins - so this would not work ??? - or so I read somewhere. 2. You were right - it was an unhandled exception - int with windows identity code !! - all the rest were wrapped :-O. 3. I have simplified the whole thing to run in a single try/catch, AND figured out that I can use an OUT parameter to return useful devugging information :-)). 4. I now run into issues with the 'DefualtNetworkCredentials' request - which fails. If I replace this with a known server login - I can get further but still get the webpermissions issue with mscorlib :-((. Why do MS always have to overcomplicate things :-((. Anyway - this might make a good example to include in your book if we can ever get it working ;-)). I'll keep plodding on but its typical MS soul-destroying tedium stopping me from getting on with 'valuable' work ;-O. Thanks Regards Graham Show quoteHide quote "Bob Beauchemin" <bobb_no_spam@SQLskills.com> wrote in message news:%23uiMobmiGHA.1204@TK2MSFTNGP02.phx.gbl... > Hmmm... I've seen both of these. ;-) > > UDF will work if you specify DataAccess=DataAccessKind.Read in the > SqlFunction attribute. Using the identity is "data access". > > "Could not revert thread token" usually just means your procedure threw an > unhandled exception and didn't call Revert. Put a try-catch block in your > SQLCLR code to catch the real exception. And/or run it in the debugger. > > Hope this helps, > Bob Beauchemin > http://www.SQLskills.com/blogs/bobb > > > "Oxns" <oxns@community.nospam> wrote in message > news:eTcm9VmiGHA.4140@TK2MSFTNGP03.phx.gbl... >> Bob, >> >> Thanks for the input - took a look at the book chapter and it had some >> useful stuff ;-)). >> >> I had already done much of the work serializer, sql scripts etc from >> other examples. The one thing I did add was your Impersonate code, which >> I was not using. This changed the fault but it still doeasn't run :-((. >> >> I am not (directly) doing SQLClient data access so had thought that a >> Function could be used - it now however tells me not. So I put the same >> code (almost) into a Stored Proc, but that tells me that it aborts >> >> Msg 10312, Level 16, State 49, Procedure StoredProcedure1, Line 0 >> >> .NET Framework execution was aborted. The UDP/UDF/UDT did not revert >> thread token. >> >> >> >> >> This is now doing my head in :-(( - this error is all but meaningless (to >> me anyway). I seem to be going through hoops here to do what is seemingly >> a simple task :-O. >> >> NB I have also secured the dll with certificates - as there was some >> thought that this was necessary for 'EXTERNAL_ACCESS'. >> >> I am rapidly coming to the conclusion that this is more trouble than its >> worth and that there must be a far better way of generating a report from >> within an SP. >> >> Thanks for your help. In case you have further interest - I have copied >> my code below : >> >> Many Thanks >> >> Graham >> >> >> using System; >> >> using System.IO; >> >> using System.Data; >> >> using System.Data.SqlClient; >> >> using System.Data.SqlTypes; >> >> using Microsoft.SqlServer.Server; >> >> using SqlReportHandler.ReportExecutionService; >> >> using System.Web.Services.Protocols; >> >> using System.Net; >> >> using System.Security.Principal; >> >> public partial class StoredProcedures >> >> { >> >> [Microsoft.SqlServer.Server.SqlProcedure] >> >> public static void StoredProcedure1(string format, string filename, >> string reportpath) >> >> { >> >> string logme = "Start, "; >> >> byte[] result = null; >> >> string historyID = null; >> >> string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"; >> >> string encoding; >> >> string mimeType; >> >> string extension; >> >> Warning[] warnings = null; >> >> ParameterValue[] reportHistoryParameters = null; >> >> string[] streamIDs = null; >> >> using (WindowsIdentity id = SqlContext.WindowsIdentity) >> >> { >> >> WindowsImpersonationContext c = id.Impersonate(); >> >> ReportExecutionService rs = new ReportExecutionService(); >> >> rs.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials; >> >> ExecutionInfo execInfo = new ExecutionInfo(); >> >> ExecutionHeader execHeader = new ExecutionHeader(); >> >> logme += "Try1, "; >> >> try >> >> { >> >> // Prepare report parameter. >> >> ParameterValue[] parameters = null; >> >> /* ParameterValue[] parameters = new ParameterValue[3]; >> >> parameters[0] = new ParameterValue(); >> >> parameters[0].Name = "EmpID"; >> >> parameters[0].Value = "288"; >> >> parameters[1] = new ParameterValue(); >> >> parameters[1].Name = "ReportMonth"; >> >> parameters[1].Value = "6"; // June >> >> parameters[2] = new ParameterValue(); >> >> parameters[2].Name = "ReportYear"; >> >> parameters[2].Value = "2004"; >> >> */ >> >> rs.ExecutionHeaderValue = execHeader; >> >> logme += "Try1.1, "; >> >> execInfo = rs.LoadReport(reportpath, historyID); >> >> logme += "Try1.2, "; >> >> // String SessionId = rs.ExecutionHeaderValue.ExecutionID; >> >> } >> >> catch (Exception ex) >> >> { >> >> logme += ex.Message + ", "; >> >> } >> >> logme += "Try2, "; >> >> try >> >> { >> >> result = rs.Render(format, devInfo, out extension, out encoding, out >> mimeType, out warnings, out streamIDs); >> >> execInfo = rs.GetExecutionInfo(); >> >> } >> >> catch (SoapException e) >> >> { >> >> logme += e.Message + ", "; >> >> } >> >> logme += "Try3, "; >> >> // Write the contents of the report to an xxx file. >> >> try >> >> { >> >> FileStream stream = File.Create(filename, result.Length); >> >> stream.Write(result, 0, result.Length); >> >> stream.Close(); >> >> } >> >> catch (Exception e) >> >> { >> >> logme += e.Message + ", "; >> >> } >> >> c.Undo(); >> >> } >> >> } >> >> }; >> >> >> "Bob Beauchemin" <bobb_no_spam@SQLskills.com> wrote in message >> news:OmTUcaliGHA.2456@TK2MSFTNGP04.phx.gbl... >>> Hi Oxns, >>> >>> SQLCLR can do webservices (at permission_set = external_access) but SQL >>> Server doesn't allow dynamic serializers, like the "add web reference" >>> mechanism eventually produces. You need to manually create your >>> serializer with sgen and catalog it to SQL Server (create assembly) >>> separately. Our new book shows how to do this in two places; one is in >>> chapter 4, which happens to be in the sample chapter at >>> http://www.SQLskills.com under "Books". It's easier than explaining the >>> whole process here (because there is an additional consideration to make >>> it run as external_access); write back if you have additional questions. >>> >>> Cheers, >>> Bob Beauchemin >>> http://www.SQLskills.com/blogs/bobb >>> >>> >>> "Oxns" <oxns@community.nospam> wrote in message >>> news:OO$NZZiiGHA.1276@TK2MSFTNGP03.phx.gbl... >>>> Peter, >>>> >>>> Thanks for the response - tried this (once I figured where to make the >>>> change - in my post build deploy script. >>>> >>>> Gave the following errors though :-(. >>>> >>>> Executing deploy script: PostDeployScript.sql ... >>>> >>>> Error: executing deploy script failed. CREATE ASSEMBLY for assembly >>>> 'SqlReportHandler.XmlSerializers' failed because assembly >>>> 'SqlReportHandler.XmlSerializers' is not authorized for PERMISSION_SET >>>> = UNSAFE. The assembly is authorized when either of the following is >>>> true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the >>>> database has the TRUSTWORTHY database property on; or the assembly is >>>> signed with a certificate or an asymmetric key that has a corresponding >>>> login with UNSAFE ASSEMBLY permission.: PostDeployScript.sql >>>> >>>> >>>> >>>> Executing deploy script: PostDeployScript.sql ... >>>> >>>> Error: executing deploy script failed. CREATE ASSEMBLY for assembly >>>> 'SqlReportHandler.XmlSerializers' failed because assembly >>>> 'SqlReportHandler.XmlSerializers' is not authorized for PERMISSION_SET >>>> = EXTERNAL_ACCESS. The assembly is authorized when either of the >>>> following is true: the database owner (DBO) has EXTERNAL ACCESS >>>> ASSEMBLY permission and the database has the TRUSTWORTHY database >>>> property on; or the assembly is signed with a certificate or an >>>> asymmetric key that has a corresponding login with EXTERNAL ACCESS >>>> ASSEMBLY permission.: PostDeployScript.sql >>>> >>>> Graham >>>> >>>> >>>> >>>> ""privatenews"" <pet***@online.microsoft.com> wrote in message >>>> news:3WJk%23UhiGHA.5184@TK2MSFTNGXA01.phx.gbl... >>>>> Hello, >>>>> >>>>> Did you try EXTERNAL_ACCESS and UNSAFE security for the assembly? >>>>> >>>>> http://msdn2.microsoft.com/en-us/library/ms189524.aspx >>>>> >>>>> Best Regards, >>>>> >>>>> Peter Yang >>>>> MCSE2000/2003, MCSA, MCDBA >>>>> Microsoft Online Partner 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,
I would recommend using WSDL.EXE (or Add Web Reference in Visual Studio) to read the web service interface to create proxy classes and methods for you. By doing this, you can invoke matching CLR methods and work on matching CLR data, with full knowledge of the data’s type. This hides the actual XML that is transmitted. When going with this approach, you will also need to use the CLR SDK tool SGEN.EXE to create and register a helper assembly for the assembly generated by WSDL, as described in: http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx Also, if you want to impersonate the currently logged in (Windows) login using SqlContext.WindowsIdentity.Impersonate this would make your procedure independant of whether or not the service principal can make network calls. Then tie the impersonated identity to the web service proxy class's Credentials property. Since RS is configured to Windows authentication by default. If you don't want t6o use impersonate the logged on user, you need to configure SQL server service startup account and make sure this account has the proper permission to the RS web site. This issue may require a bit more in depth attention and may fall under the umbrella of Advisory Services. Microsoft now offers short-term and proactive assistance for specific planning, design, development or assistance with installing, deploying, and general "how to" advice via telephone. For more information: http://support.microsoft.com/default.aspx?scid=fh;en-us;advisoryservice Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner 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. Peter,
Thaks for the comments. I am indeed doing all the things you are recommending (I think) : 1. The very first thing I did was to use VS2005 to create the web service proxy code. 2. I am using 'Impersonate'. 3. The postbuild creates the xmlserialization assembly 4. The assembly is being created with 'EXTERNAL_ACCESS' or 'UNSAFE' (I am trying both). 5. The assembly is signed with a certificate created for the job - based on a matchng trust cert which is also installed on the server. Basically - I have taken on-board all the advice so far offered from all sources but still fall foul of this one :-((. More than likely something stupid i have missed I guess :-O. I will probably open up one of my 'free' MSDN support incidents on this one as it doesn't want to go away ;-O. Thanks again Regards Graham ""privatenews"" <pet***@online.microsoft.com> wrote in message Show quoteHide quote news:5i8Gbb4iGHA.4688@TK2MSFTNGXA01.phx.gbl... > Hello, > > I would recommend using WSDL.EXE (or Add Web Reference in Visual Studio) > to > read the web service interface to create proxy classes and methods for > you. > By doing this, you can invoke matching CLR methods and work on matching > CLR data, with full knowledge of the data's type. This hides the actual > XML that is transmitted. When going with this approach, you will also > need > to use the CLR SDK tool SGEN.EXE to create and register a helper assembly > for the assembly generated by WSDL, as described in: > http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx > > > Also, if you want to impersonate the currently logged in (Windows) login > using SqlContext.WindowsIdentity.Impersonate this would make your > procedure > independant of whether or not the service principal can make network > calls. > Then tie the impersonated identity to the web service proxy class's > Credentials property. > > Since RS is configured to Windows authentication by default. If you don't > want t6o use impersonate the logged on user, you need to configure SQL > server service startup account and make sure this account has the proper > permission to the RS web site. > > > This issue may require a bit more in depth attention and may fall under > the > umbrella of Advisory Services. Microsoft now offers short-term and > proactive assistance for specific planning, design, development or > assistance with installing, deploying, and general "how to" advice via > telephone. For more information: > > http://support.microsoft.com/default.aspx?scid=fh;en-us;advisoryservice > > Best Regards, > > Peter Yang > MCSE2000/2003, MCSA, MCDBA > Microsoft Online Partner 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,
OK finally got this working. Anyone wants to see a more full explanation see posting entitled : Security settings for SSRS webservice access from SQLCLR Thread started 8th Jun on the 'security' group Basically - yes - doing something stupid - like not recreating the base assembly each time - because VS2005 did this transparently (no script) first time round :-O. I was changing permission ONLY on the XmlSerializers.dll assembly :-((. Thanks everyone who helped on this ;-)). Regards Graham Show quoteHide quote "Oxns" <oxns@community.nospam> wrote in message news:eu$ejA5iGHA.4884@TK2MSFTNGP03.phx.gbl... > Peter, > > Thaks for the comments. I am indeed doing all the things you are > recommending (I think) : > > 1. The very first thing I did was to use VS2005 to create the web service > proxy code. > > 2. I am using 'Impersonate'. > > 3. The postbuild creates the xmlserialization assembly > > 4. The assembly is being created with 'EXTERNAL_ACCESS' or 'UNSAFE' (I am > trying both). > > 5. The assembly is signed with a certificate created for the job - based > on a matchng trust cert which is also installed on the server. > > > Basically - I have taken on-board all the advice so far offered from all > sources but still fall foul of this one :-((. > > More than likely something stupid i have missed I guess :-O. > > I will probably open up one of my 'free' MSDN support incidents on this > one as it doesn't want to go away ;-O. > > Thanks again > > Regards > > Graham > > ""privatenews"" <pet***@online.microsoft.com> wrote in message > news:5i8Gbb4iGHA.4688@TK2MSFTNGXA01.phx.gbl... >> Hello, >> >> I would recommend using WSDL.EXE (or Add Web Reference in Visual Studio) >> to >> read the web service interface to create proxy classes and methods for >> you. >> By doing this, you can invoke matching CLR methods and work on matching >> CLR data, with full knowledge of the data's type. This hides the actual >> XML that is transmitted. When going with this approach, you will also >> need >> to use the CLR SDK tool SGEN.EXE to create and register a helper assembly >> for the assembly generated by WSDL, as described in: >> http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx >> >> >> Also, if you want to impersonate the currently logged in (Windows) login >> using SqlContext.WindowsIdentity.Impersonate this would make your >> procedure >> independant of whether or not the service principal can make network >> calls. >> Then tie the impersonated identity to the web service proxy class's >> Credentials property. >> >> Since RS is configured to Windows authentication by default. If you don't >> want t6o use impersonate the logged on user, you need to configure SQL >> server service startup account and make sure this account has the proper >> permission to the RS web site. >> >> >> This issue may require a bit more in depth attention and may fall under >> the >> umbrella of Advisory Services. Microsoft now offers short-term and >> proactive assistance for specific planning, design, development or >> assistance with installing, deploying, and general "how to" advice via >> telephone. For more information: >> >> http://support.microsoft.com/default.aspx?scid=fh;en-us;advisoryservice >> >> Best Regards, >> >> Peter Yang >> MCSE2000/2003, MCSA, MCDBA >> Microsoft Online Partner 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. >> >> > >  
Other interesting topics
Help with looping through records in stored procedure
Turn this SP into a view! Tough SQL problem, need expert advice!!! Advice Requested : Trying to write portable SQL Date Parsing using T-SQL find the first row of ordered records that sum is less than a cert Insert by Parameter trigger will not execute Help! Can't pass names with apostrophe from ASP to SQL7 proc 2 questions on Sql server query |
|||||||||||||||||||||||
