That Assembly Does Not Allow Partially Trusted Callers.
May 25, 2006
I have a report that references a custom assembly. The assembly loads an xml file from a web site in the code OnInit. The reports designer the code runs fine, because it is called with full trust. I deployed the report to our test sever, and copied the two dll files to c:Program FileMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServicein. I have added the following to the rssrvpolicy file.
<PermissionSet class="NamedPermissionSet"
version="1"
Name="LexLibrary"
Description="Used by reports to access the Lex Library">
<IPermission class="SecurityPermission"
version="1"
Flags="Assertion, Execution "/>
<IPermission class="WebPermission"
version="1">
<ConnectAccess>
<URI uri="http://lcserver3/lex/boe.xml"/>
</ConnectAccess>
</IPermission>
</PermissionSet>
When I try to run the report on the server I get €œAn error occurred while executing OnInit: That assembly does not allow partially trusted callers. (rsErrorInOnInit) €œ
Can anyone see what I€™m missing?
I have successfully compiled & deployed the custom security extension. Currently I am having some runtime error when using the added Membership.Data.dll for authentication & authorization.
Membership.Data.dll * signed with a strong name key * [assembly: AllowPartiallyTrustedCallers] is added * it uses Microsoft.Practices.EnterpriseLibrary.Data.dll for database connection
I have also added connectionstring to web.config(s) under ReportManager and ReportServer.
Here is the exception:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Security.SecurityException: That assembly does not allow partially trusted callers. at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed) at Membership.Data.DataProvider.Acquire(String DatabaseName) at Microsoft.Samples.ReportingServices.CustomSecurity.AuthenticationUtilities.VerifyPassword(String suppliedUserName, String suppliedPassword) in C:Program FilesMicrosoft SQL Server90SamplesReporting ServicesExtension SamplesFormsAuthentication SamplecsFormsAuthenticationAuthenticationUtilities.cs:line 116 at Microsoft.Samples.ReportingServices.CustomSecurity.AuthenticationExtension.LogonUser(String userName, String password, String authority) in C:Program FilesMicrosoft SQL Server90SamplesReporting ServicesExtension SamplesFormsAuthentication SamplecsFormsAuthenticationAuthenticationExtension.cs:line 70 at Microsoft.ReportingServices.WebServer.RSCustomAuthentication.LogonUser(String userName, String password, String authority) at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.LogonUser(String userName, String password, String authority) at Microsoft.ReportingServices.WebServer.ReportingService2005.LogonUser(String userName, String password, String authority) The action that failed was: LinkDemand The assembly or AppDomain that failed was: Membership.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=<key>The method that caused the failure was: Microsoft.Practices.EnterpriseLibrary.Data.Database CreateDatabase(System.String) The Zone of the assembly that failed was: MyComputer The Url of the assembly that failed was: file:///c:/Program Files/Microsoft SQL Server/MSSQL.4/Reporting Services/ReportServer/bin/Membership.Data.DLL --- End of inner exception stack trace ---
Any ideas? I assume that Microsoft.Practices.EnterpriseLibrary.Data.dll is in GAC so i shouldn't have to worry about [AllowPartiallyTrustedCallers]
I work with February CTP of SqlServer 2008. I have an Assembly with several UDTs inside. Version of assembly is 1.0.* I use CREATE ASSEMBLY statement to register this assembly, and it runs without any errors. Then I rebuild CLR solution without doing any changes in source code. In that case the only difference between new and old assemblies is version (difference in fourth part of version). Then I try to update assembly in SqlServer. I use ALTER ASSEMBLY <name> FROM <path> WITH PERMISSION_SET = UNSAFE, UNCHECKED DATA statement for this. Statement runs with error: Msg 6509An error occurred while gathering metadata from assembly €˜<Assembly name>€™ with HRESULT 0x1. I found the list of condition for ALTER ASSEMBLY in MSDN: ALTER ASSEMBLY statement cannot be used to change the following: · The signatures of CLR functions, aggregate functions, stored procedures, and triggers in an instance of SQL Server that reference the assembly. ALTER ASSEMBLY fails when SQL Server cannot rebind .NET Framework database objects in SQL Server with the new version of the assembly. · The signatures of methods in the assembly that are called from other assemblies. · The list of assemblies that depend on the assembly, as referenced in the DependentList property of the assembly. · The indexability of a method, unless there are no indexes or persisted computed columns depending on that method, either directly or indirectly. · The FillRow method name attribute for CLR table-valued functions. · The Accumulate and Terminate method signature for user-defined aggregates. · System assemblies. · Assembly ownership. Use ALTER AUTHORIZATION (Transact-SQL) instead. Additionally, for assemblies that implement user-defined types, ALTER ASSEMBLY can be used for making only the following changes: · Modifying public methods of the user-defined type class, as long as signatures or attributes are not changed. · Adding new public methods. · Modifying private methods in any way.
But I haven€™t done any changes in source code, so new version of assembly satisfies all this conditions. What could be the reason for such behavior? P.S. I€™ve got the same error, if I add or change any method in assembly before rebuilding.
I am trying to get a function I created in VB 5 for Access and Excel to work in SQL 2005. I was able to update the old VB code to work in VB 2005. I compiled and made a .dll, and I was able to register the new Assembly in SQL Server. When I try to create the Function, I get an error:
AS EXTERNAL NAME FluidProps.[FluidProps.FluidProperties.Fluids].Temperature
Error returned:
Msg 6573, Level 16, State 1, Procedure Temperature, Line 21
Method, property or field 'Temperature' of class 'FluidProps.FluidProperties.Fluids' in assembly 'FluidProps' is not static.
Here is the code (part of it) in the VB class:
Header:
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
Imports System.Security
Imports System.Security.Permissions
Namespace FluidProperties
'Option Strict Off
'Option Explicit On
Public Partial Class Fluids
Function:
Function Temperature(ByRef FluidName As Object, ByRef InpCode As Object, ByRef Units As Object, ByRef Prop1 As Object, Optional ByRef Prop2 As Object = Nothing) As Object
If I change the Function Temperature to Static, I get an error that functions cannot be Static. Its been a long time since I created the code and am having a hard time in my older age of getting the cobwebs out to make it work.
I have no problem getting the function to work if I call it from a VB form....so what do I need to do to get it to work on data in my SQL server?
I previously had an ASP.NET 1.1 site running on my IIS 6.0 server (not the default website) with Reporting Services running in a subdirectory of that website. I recently upgraded to ASP.NET 2.0 for my website and was greeted with an error when trying to view a report. The error was very non-descript, but when I checked the server logs, it recorded the details as "It is not possible to run two different versions of ASP.NET in the same IIS process. Please use the IIS Administration Tool to reconfigure your server to run the application in a separate process."
First of all, I could not figure out where and how to do this. Secondly, I decided to try to also change the Reporting Services folders to run ASP.NET 2.0 and when I did, I was greeted with the following message when attempting to view a report:
"Failed to load expression host assembly. Details: StrongName cannot have an empty string for the assembly name."
I am a bit paranoid about what I just did to my SQL Server 2005 with this CLR experiment.
I created a Class Lib in C# called inLineLib that has a class Queue which represents an object with an ID field.
in another separate namespace called inLineCLRsql, I created a class called test which will hold the function to be accessed from DB, I referenced and created an instances of the Queue class, and retrieve it's ID in a function called PrintMessage.
to access this from the db, I attempted to create an assembley referencing inLineCLRsql.dll. This didn't work as it complained about inLineLib assembly not existing in the db. I then attempted to create an assembley for inLineLib but it barfed saying System.Management assembly not created.
so what I did is (and this is where I need to know if I just ruined sql server or not):
1- ALTER DATABASE myDB SET TRUSTWORTHY ON;.
2- CREATE ASSEMBLY SystemManagement
FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Management.dll'
WITH PERMISSION_SET = UNSAFE
3- CREATE ASSEMBLY inLineLibMaster
FROM 'D:inLineServerinLineLibinDebuginLineLib.dll'
WITH PERMISSION_SET = unsafe
4- and finally
CREATE ASSEMBLY inLineLib
FROM 'D:inLineServerCLRSQLinlineCLRsqlinDebuginlineCLRsql.dll'
WITH PERMISSION_SET = SAFE
Everything works after those steps (which took some trial and error). I can create a sproc like:
CREATE PROC sp_test AS
EXTERNAL NAME inLineLib.[inlineCLRsql.test].PrintMessage
and it returns the Queue ID
Is there anything unadvisable about the steps above?
Hello everyone, I had a previous issue with if...then....etc statement, but I got it to work, so thats a good thing. The only problem is that it works only when i have a single line displaying, if its more than 1, it dont work any ideas why???? My report is table, broken into 2 groups, one by department, then by last name. My thought is the Sum(Fields!AMT_EARNED.Value) still giving me issues.....but I dont know....
We have written a test CRL stored procedure to test replacing one of our complex stored procedures but can€™t get it deployed to our SQL server that hosts a mirrored configuration of our production database (very locked down). It works fine on our development instances (not very locked down). It only references the default assemblies that were added when we created the project. All it does is use Context Connection=true to get data, loops though some records and returns the data using SQLContext. CLR is enabled on SQL server, the assembly is strongly signed, and we tried deploy using the binary string with the SAFE setting.
CREATE ASSEMBLY for assembly 'SQLCLRTest2' failed because assembly 'SQLCLRTest2' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message [ : SQLCLRTest2.StoredProcedures::GetLift][mdToken=0x600001e] Type load failed. [token 0x02000008] Type load failed.
How to dynamically and partially Update the webpage? The content ofthe part of the page is from the user's requerement and get data fromthe server side.The purpose is to speed up the page update. We have a page that mostof content are static, only small part is changed per user's selectand the replying data need to get from server side database. Thanksfor help. Gene
Can we do database shrink in volume not full to manage spaces. Only few gb spaces are available, so i want to shrink the database. How to do that. Â DB size-775 Gb Free Dis size-156 Gb out of 931 GB
I am trying to deploy a Database Project with Visual Studio 2005 and SQL Server 2005 Standard. I import €œSystem.IO€? and have therefore set the permission levels to EXTERNAL_ACCESS.
I am receiving the same error message that many folks have received.
CREATE ASSEMBLY for assembly 'Images' failed because assembly 'Images' 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. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem. Images.
My CLR access is €œon€?
I have tried
1) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to [BuiltinAdministrators]. 2) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to €œMy Windows Authentication ID€?. 3) Run ALTER DATABASE MYDATABASE SET TRUSTWORTHY ON 4) In Visual Studio .NET 2005 Set the permission levels to €˜external€™ 5) Tried BuiltinAdministrators and my SQL Server Windows Authenticated Login ID for the ASSEMBLY OWNER.
I can compile BUT NOT DEPLOY
Any help would be greatly appreciated. Regards Steve
I was trying to understand how VS.NET2005 was deploying .NET CLR assemblies to SQL2005 so I ran a trace and found some interesting results.
VS.NET creates some SQL that looks pretty interesting:
CREATE ASSEMBLY [AssemblyNameHere] FROM 0x4D5A90000300000004000000FFFF000......<continue binary data> WITH PERMISSION_SET = EXTERNAL_ACCESS
Boy howdy!
I have tried to reproduce this and create my own deployment application but I cant figure out how they create this binary stream. The info in BOL is not much help and I have not found any samples anywhere on how to create this stream in c#.
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. Please provide separate solutions for SqlServer2000/2005. I have four tables namely – Forums,Topics,Threads and Messages in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- SELECT Forums.forumid AS ForumID,Topics.topicid AS TopicID,Topics.name AS TopicName,LastPosts.date as LastPostDate,LastPosts.author AS Author, (select count(threadid) from Threads where Threads.topicid=Topics.topicid) as NoOfThreads FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid LEFT OUTER JOIN ( SELECT Topics.forumid,Threads.topicid,Messages.date,Messages.author FROM Topics INNER JOIN Threads ON Topics.topicid = Threads.topicid INNER JOIN Messages ON Threads.threadid=Messages.threadid WHERE Messages.date=(SELECT MAX(date) FROM Messages WHERE Messages.threadid = Threads.threadid) ) as LastPosts ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid Whose result set is as below:-
forumid topicid name LastPostDate author NoOfThreads
5 17 General NULL NULL 0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the message) from the Messages table as shown above. When I use the query by using only three tables namely Forums,Topics and Threads, I get the correct result. The query is as:- SELECT Topics.forumid AS ForumID,Forums.name AS ForumName,Topics.topicid as TopicID,Topics.name as TopicName, LastPosts.author AS Author,LastPosts.lastpostdate AS LastPost, (select count(threadid) from threads where Threads.topicid=Topics.topicid) as NoOfThreads FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid LEFT OUTER JOIN ( SELECT Topics.forumid, Threads.topicid, Threads.lastpostdate, Threads.author FROM Threads INNER JOIN Topics ON Topics.topicid = Threads.topicid WHERE Threads.lastpostdate IN (SELECT MAX(lastpostdate) FROM threads WHERE Topics.topicid = Threads.topicid) ) AS LastPosts ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid Whose result set is as:-
5 General 17 General NULL NULL 0 The scripts for creating the tables and inserting test data is as follows in an already created database:- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__35BCFE0A]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__35BCFE0A GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__34C8D9D1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__34C8D9D1 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Messages__thread__33D4B598]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Messages] DROP CONSTRAINT FK__Messages__thread__33D4B598 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Forums] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Topics] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Threads] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Messages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Messages] GO CREATE TABLE [dbo].[Forums] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Topics] ( [topicid] [int] IDENTITY (1, 1) NOT NULL , [forumid] [int] NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Threads] ( [threadid] [int] IDENTITY (1, 1) NOT NULL , [topicid] [int] NOT NULL , [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [replies] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastpostdate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Messages] ( [msgid] [int] IDENTITY (1, 1) NOT NULL , [threadid] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [date] [datetime] NULL , [message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[Forums] ADD PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD PRIMARY KEY CLUSTERED ( [topicid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD PRIMARY KEY CLUSTERED ( [threadid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Messages] ADD PRIMARY KEY CLUSTERED ( [msgid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD FOREIGN KEY ( [forumid] ) REFERENCES [dbo].[Forums] ( [forumid] ) GO ALTER TABLE [dbo].[Threads] ADD FOREIGN KEY ( [topicid] ) REFERENCES [dbo].[Topics] ( [topicid] ) GO ALTER TABLE [dbo].[Messages] ADD FOREIGN KEY ( [threadid] ) REFERENCES [dbo].[Threads] ( [threadid] ) GO ------------------------------------------------------ insert into forums(name,description) values('Developers','Developers Forum'); insert into forums(name,description) values('Database','Database Forum'); insert into forums(name,description) values('Desginers','Designers Forum'); insert into forums(name,description) values('Architects','Architects Forum'); insert into forums(name,description) values('General','General Forum'); insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall'); insert into topics(forumid,name,description) values(1,'JSP','Topic JSP'); insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans'); insert into topics(forumid,name,description) values(1,'Swings','Topic Swings'); insert into topics(forumid,name,description) values(1,'AWT','Topic AWT'); insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services'); insert into topics(forumid,name,description) values(1,'JMS','Topic JMS'); insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML'); insert into topics(forumid,name,description) values(1,'Javascript','Javascript'); insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle'); insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server'); insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL'); insert into topics(forumid,name,description) values(3,'CSS','Topic CSS'); insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML'); insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices'); insert into topics(forumid,name,description) values(4,'Longue','Longue'); insert into topics(forumid,name,description) values(5,'General','General Discussion'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM'); insert into messages(threadid,author,date,message) values(1,'a@b.com','1/27/2008 2:44:39 PM','Where can I find Java tutorials.'); insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:45:28 PM','Please visit www.java.sun.com'); insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:46:41 PM','Do a Google serach for more tutorials.'); insert into messages(threadid,author,date,message) values(2,'x@y.com','1/27/2008 2:48:53 PM','Please provide some Beginner tutorials.'); insert into messages(threadid,author,date,message) values(3,'p@q.com','1/27/2008 3:12:51 PM','How many finds of layout managers are there?'); insert into messages(threadid,author,date,message) values(2,'l@m.com','2/2/2008 1:06:06 PM','Search on Google.');
I have a SSIS which have a main connection to a SQL Server 2005 DB, and also another connection to a Oracle database, and fetch data from the Oracle db.
I have the 2 connection managers, and everything runs fine when I test it in dev studio. However, once I imported it to the DB, and run it on command line, I get the "Class not registered" error.
Log file as follows:
C:>dtexec /DTS "MSDB ST1INST101CRDA_D_GIM_CONN_TEST" /SERVER crpnycmsq34q /CONNECTION "CRD_CM";""Data Source=crpnycmsq34qST1INST101;Initial Catalog=CRD;Provider=SQLNCLI.1;Integrated Security=SSPI;"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:23:53 PM Info: 2008-02-28 13:23:53.91 Code: 0x40016040 Source: CRDA_D_GIM_CONN_TEST Description: The package is attempting to configure from SQL Server using the configuration string ""CRD_CM";"[dbo].[CMG_SSIS_CONFIG]";"GIM_SOURCE";". End Info Info: 2008-02-28 13:23:54.03 Code: 0x40016040 Source: CRDA_D_GIM_CONN_TEST Description: The package is attempting to configure from SQL Server using the configuration string ""CRD_CM";"[dbo].[CMG_SSIS_CONFIG]";"GIM_USER";". End Info Info: 2008-02-28 13:23:54.05 Code: 0x40016040 Source: CRDA_D_GIM_CONN_TEST Description: The package is attempting to configure from SQL Server using the configuration string ""CRD_CM";"[dbo].[CMG_SSIS_CONFIG]";"GIM_PWD";". End Info Error: 2008-02-28 13:23:54.55 Code: 0xC0202009 Source: CRDA_D_GIM_CONN_TEST Connection manager "GIM" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2008-02-28 13:23:54.56 Code: 0xC00291EC Source: Execute SQL Task Execute SQL Task Description: Failed to acquire connection "GIM". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2008-02-28 13:23:54.58 Code: 0x80019002 Source: CRDA_D_GIM_CONN_TEST Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:23:53 PM Finished: 1:23:54 PM Elapsed: 1.547 seconds
I DID TRIED MY BEST to search before posting, and most of them are having problem with 32 bit and 64 bit executions. However, in my case, both are 32 bit (as shown in the log file).
In the above test, I'm setting DoNotSaveSensitiveData, and then fetch it with package configuration.
To make sure the package configuration is not creating pblm for me, I also tried to hardcode everything in the package, and then encrypt the whole ssis with password, and run the package with the /DECRYPT option, so that everything is already intact.
But I still get the same error. Log file as follows.
C:>dtexec /DTS "MSDB ST1INST101CRDA_D_GIM_CONN_TEST" /SERVER crpnycmsq34q /De "painful" /CONNECTION "CRD_CM";""Data Source=crpnycmsq34qST1INST101;Initial Catalog=CRD;Provider=SQLNCLI.1;Integrated Security=SSPI;"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:24:25 PM Error: 2008-02-28 14:24:27.11 Code: 0xC0202009 Source: CRDA_D_GIM_CONN_TEST Connection manager "GIM" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2008-02-28 14:24:27.14 Code: 0xC00291EC Source: Execute SQL Task Execute SQL Task Description: Failed to acquire connection "GIM". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2008-02-28 14:24:27.14 Code: 0x80019002 Source: CRDA_D_GIM_CONN_TEST Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:24:25 PM Finished: 2:24:27 PM Elapsed: 1.422 seconds
Can anyone suggest what is missing ?? or what can be done ??
As far as I know temp tables/objects will be created inside the default filegroup of the partially contained database and not in tempdb. Is it possible to either define a set of files dedicated to temp objects or define a second partially contained database dedicated to temp objects like tempdb?
When I try to debug the break points will always say the source code is different from the current version, but the custom component in the GAC has the new version number. The other strange thing is the toolbox will not reset to the original version meaning it will not remove the custom components. The funny thing is after I compile the custom components and restart VS the custom component runs with the new code changes. I can see the new features I added, but the debugger and toolbox still seem to be broken.
I have tried the following 1) Reset the tool box. 2) uninstall all my custom dll from the GAC €œC:WINDOWSassembly€? 3) remove all my custom dll from €œC:Program FilesMicrosoft SQL Server90DTSPipelineComponents€? 4) restart VS 2005 5) reselect the custom components. 6) reboot my computer.
It seem like VS has another cache. For the tool box or something.
We have a table with 500+ columns. The data is non-normalized, i.e. there are four groups of fields for for "people", followed by data that applies to all people in the row (a "household").For ad-hoc queries, and because I wanted to index columns within each person (person 1's age, person 2's age, etc.), I used UNION:
SELECT P1Firstname AS FirstName, P1LastName as LastName, P1birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns> UNION SELECT P2Firstname AS FirstName, P2LastName as LastName, P2birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>
I could get at least the P1... P2... P3... columns with PIVOT, but then I believe I'd have to JOIN back to the row anyway for the "household" columns. Performance of UNION good, but another person here chose to use PIVOT instead.I can' find any articles on PIVOT vs. UNION for "de-flattening".
I have an assembly, call it A1, that I've deployed to a SQL Server 2005 database. I can use the managed stored procedures from A1 in SQL Server no problem.
In A1 there is a bit of code which uses the Assembly.Load() method, so load another assembly and use instances of class found in that external assembly. However, when I run the managed stored proc in A1 that uses Assembly.Load() I get the error:
Could not load file or assembly 'A1, Version=1.0.0.0, Culture=neutral,PublicKeyToken='????' or one of its dependencies. The system cannot find the file specified.
(note: for security I've changed some of the above line).
I re-built the project, re-deployed it and ran the code in SQL Server - it worked. I could create an instance of a System.Data.DataSet for example. So why can't I load my own custom assembly? My assembly does have a strong name and it's installed in the GAC. I wrote a console app to try and Assembly.Load() my custom assembly and that worked fine (it was also running on the same server as the SQL Server).
So it's defiantely the SQL Server that can't 'see' my customer assembly. What do I need to do this assembly so that SQL Server will allow me to Assembly.Load it, just as it can with System.Data?
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate ORDER BY t.topicid ASC,th.lastpostdate DESC Whose result set is as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 On modifying the query to:- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid) ORDER BY t.topicid ASC,th.lastpostdate DESC I get the result set as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. The scripts for creating the tables and inserting test data is as follows in an already created database:- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Forums] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Threads] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Topics] GO CREATE TABLE [dbo].[Forums] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Threads] ( [threadid] [int] IDENTITY (1, 1) NOT NULL , [topicid] [int] NOT NULL , [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [replies] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastpostdate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Topics] ( [topicid] [int] IDENTITY (1, 1) NOT NULL , [forumid] [int] NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Forums] ADD PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD PRIMARY KEY CLUSTERED ( [threadid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD PRIMARY KEY CLUSTERED ( [topicid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD FOREIGN KEY ( [topicid] ) REFERENCES [dbo].[Topics] ( [topicid] ) GO ALTER TABLE [dbo].[Topics] ADD FOREIGN KEY ( [forumid] ) REFERENCES [dbo].[Forums] ( [forumid] ) GO ------------------------------------------------------ insert into forums(name,description) values('Developers','Developers Forum'); insert into forums(name,description) values('Database','Database Forum'); insert into forums(name,description) values('Desginers','Designers Forum'); insert into forums(name,description) values('Architects','Architects Forum'); insert into forums(name,description) values('General','General Forum'); insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall'); insert into topics(forumid,name,description) values(1,'JSP','Topic JSP'); insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans'); insert into topics(forumid,name,description) values(1,'Swings','Topic Swings'); insert into topics(forumid,name,description) values(1,'AWT','Topic AWT'); insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services'); insert into topics(forumid,name,description) values(1,'JMS','Topic JMS'); insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML'); insert into topics(forumid,name,description) values(1,'Javascript','Javascript'); insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle'); insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server'); insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL'); insert into topics(forumid,name,description) values(3,'CSS','Topic CSS'); insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML'); insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices'); insert into topics(forumid,name,description) values(4,'Longue','Longue'); insert into topics(forumid,name,description) values(5,'General','General Discussion'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
I have a Web application in asp.net 1.1Iam using windows authentication. The application is on IIS on MachineA. When i try to access this from MachineB as http://MachineA/test/test.aspx, it gives me the error "login failed for user 'null' : not associated with a trusted sql connection"Both MachineA and MachineB are on the same domain & iam not using any sql authentication. Could someone suggest me where i might have gone wrong. Web.config has authentication as 'windows', allow users = "*" and Identity impersonation = trueOn IIS, the vitual directory of 'test' application has Directory secuirty set to 'Integrated Security'Please let me know if someone had dealt with similar scenario. Thanks.
Hey all, not sure if this is even possible but is there a way to connection an SQL server with ASP.NET using my username and password as the trusted connection? As I am a trusted connection but the ASP.NET working process isnt. Anything can be done about this apart from addeing the ASP.NET account as a trusted connection?
I was just wondering about the old error:"user not associated with a trusted connection"I know how to solve it, but I dont really understand what im doing. If my connection string is like:Trusted_Connection=true;Initial Catalog=jobitdev;Data Source=192.168.109.4;Packet Size=4096;then how validation is done on the sql server side of things? If i specify the "Trusted_connection" property what does the server do to validate the user? I'm assuming that the user it checks is the current windows user?
I have just installed MSSQL 2000 on Windows 2000. what I am finding is that I cannot open an isql (or query analyzer) session using the sql login (i am successful when i use NT authentication). The error message I get is as follows
Msg 18452, Level 14, State 1: Login failed for user 'xxx'. Reason: Not associated with a trusted SQL Server connection. DB-Library: Login incorrect.
MSDN talks about setting the registry entries differently, but that seems to be only for SQL 7.
I'm attempting to set up a dts transfer SQL 7 box to SQL 7 box. These two servers are on two separate NT domains with no trust relationship, and I will be sending the info across a VPN.
Anyone out there have a similar situation? Offer any recommendations, pitfalls, ports used, ways to do this??? I'd appreciate any ANY ideas on how to make this work. Thanks in advance. -Tricia
Can anyone please tell me how to create a trusted connection?. I am from Unix world and NT is still kind of new to me. Let say my SQL server is located in this machine residing in domain X and I want a NT user, ABC, who is in domain Y to have acess to my server. What do I need to do?. Many thanks.
Is there any way to connect to SQL Server from a non trusted domain. Passthrough authentication works for other NT Server resources (like exchange folder, printers, shared folders), but SQL Server 7.0 does not seem to accept this passthrough authentication (where the username and password are the same in both domains). There is no internet access required.
Does anyone know how to create trusted connections?. What I want to do is to have connection to a sql server that's in a different domain as I am (a NT workstation). I tried to create a login id on the server with my nt id but got an error:
I operate the above environment and have been advised due to application issues to upgrade my ODBC to 3.6 from 2.x
I upgraded one of my clients and am unable to use the already successfully configured and operational Integrated Security, It will only operate with Standard security.
However the other clients with ODBC 2.x are still able to use Integrated Security.
The configuration of both ODBC's in so far as is possible (different screens and entries) is identical. I confirm the after the 3.6 ODBC is installed the end summary screen confirms a Trusted configuration. However the test connection utility fails with the well and guaranteed to give a hernia "not associated with a trusted SQL connection".
With anticipation and thanks.
Peter Dear
PS Why when I do a spell check on ODBC's, does it return "Oddballs"?
What does it mean when a users is connected but it is a "non-trusted connection". This is showing up in the log file after my users connect. How does this effect the system/user? We have been experiencing speed issues and I was wondering if this might be a possible source of the problem.
