Load Multiple Signed Assemblies
Aug 18, 2006
I am trying to load multiple strongly named assemblies into the same database which are signed with the same .snk file (signed in Visual Studio). I use the following code to create an asymmetric key and login as Books Online recommends:
CREATE ASYMMETRIC KEY SQLCLRKey FROM FILE = 'D:dbainAssembly.dll'
CREATE LOGIN CLRAssembler FROM ASYMMETRIC KEY SQLCLRKey
GRANT UNSAFE ASSEMBLY TO CLRAssembler
GRANT EXTERNAL ACCESS ASSEMBLY TO CLRAssembler
REVOKE CONNECT SQL FROM CLRAssembler
Do I need to create a new login and asymmetric key for each assembly I load? If so, do I need to sign each with a different key because its giving me an error message when I try to create 2 separate asymmetric keys/logins from 2 different assemblies which have been signed with the same .snk file.
The only way I've gotten everything to load properly is to create a separate key for each assembly and sign each, then create separate logins and asymmetric keys in the database.
Is this the only way to do this? Or am I missing something?
View 5 Replies
ADVERTISEMENT
Feb 17, 2006
I have SQL Server 2003 Standard and am attempting to use DTS for a data load/transformation and I€™m not sure if I am using the right tool for the job. I have a somewhat denormalized Access database that has to be loaded into a normalized SQL Server database. Values from one row in any of the source tables generally need to be separated and inserted into several destination (SQL Server) tables. There are no unique ids in the source data since it is coming from a 3rd party and the tables are not related to others. I€™ve created a DTS Package and have the beginnings of several Transform Data Tasks. Each destination table has an Identity id column, which is calculated automatically. The roadblock I€™ve run into is that I can€™t figure out how to take each newly created ID and insert it into a new row in another table as a foreign key. Basically, I have to move data from a single input row to new rows in multiple destination tables and create ids (PK, FK) that tie these tables together. The Transform Data Task only allows me to reference one source and one destination, not multiple destinations. I hope this makes sense. Any suggestions would be appreciated.
Here€™s a simple example that may help illustrate the problem. My database is much more complex.
Input table is called parcels and each row has three columns: Address, Owner, and Legal_Description.
Output database has two tables that will receive this data: Parcel table will have the Address, Owner and Parcel_ID (auto calculated). Legal table will have Legal_Description, Legal_Desc_ID (auto calculated), and Parcel_ID.
When the row is inserted into the Parcel table, the newly auto calculated Parcel_ID has to be captured. Next create a row into the Legal table and insert the Parcel_ID so the two rows are related.
How can I do this through DTS? Thanks for any suggestions, code snippets, or references.
View 1 Replies
View Related
Mar 10, 2006
Can anybody can tell me how to do self-signed certificate on sql server? What is it ? Do we really need?
Many thanks.
View 12 Replies
View Related
Aug 30, 2012
I'm trying to add an assembly to my SSDT project, and in my assembly, it requires EXTERNAL access, which also means that the assembly has to be signed, and an asymmetric key must be created from the assembly. Â I can't figure out how to get this to work from the SSDT project.
View 8 Replies
View Related
Mar 17, 2007
Hi all,
We just bought a server with 2 quad core xeon processors to host a web application with reporting services.
Our reports are quite heavy. I wrote custom data processing extensions for them and some take up to 45 minutes to render.
When a web user runs more than one report at the same time, only one of the eight available cores is processing the request. When two different web users are each running a single report, the same thing happens. It looks like no matter how many reports are running at the same time, only one core chokes.
I would like to be able to execute each report request on a different core. How could a change my data processing extension or my Reporting Services settings to achieve that?
Any help would be appreciated,
Dom.
View 9 Replies
View Related
Apr 23, 2008
Hi Evry one,
I Have Multiple Flat Files in Source Folder(They have Naming Conventions With Todays Date ex: Flatfile_20082204_1,Flatfile_20082204_2,Flatfile_20082204_3 ),
I need to Extract Each and Evry file by Dynamically, and Transform the Flat File then load that Flat file into the Destination Folder with Standard Prefix and Todays Date with a Sequence No ex:Flatfile_20082304_A,Flatfile_20082304_B, Flatfile_20082304_C
Please HELP Me
Thanks In Advance.
View 20 Replies
View Related
Apr 21, 2008
Hello,
I have a SQL 2005 test system set up that I need to test with protocol encryption, and had in mind to peform this test using a self-signed SSL certificate. I have read the guides and requirements from MS as well as the few blogs I could find on the subject and I've *almost* gotten it to work, but I'm failing at what seems to be the last step - getting clients to connect.. Here is what I've done:
-Created a self-signed certificate (I've tried using SSL Diagnostics from the IIS Diagnostics package, makecert.exe from .NET SDK, and OpenSSL) with the 'Server Authentication' purpose, with a CN that matches the server name, along with a private key. I've imported the cert in the local computer's personal store (also in LC's Trusted Root).
-SQL Server Configuration Manager sees the certificate and allows selecting it in the configuration. I've done this and selected 'Force Encryption' in the protocol settings.
-Restarted SQL Server, errorlog says 'The certificate was sucessfully loaded for encryption'.
-Tried connecting locally using SSMS and setting encryption: No problem.
-Exported the cert from the store and imported it into the LC's Trusted Root store on the client comp.
Here's where the problems begin.
1: Client is still apparently able to connect *unencrypted* to the SQL Server (I tested with SSMS on the client machine, it states under connection properties that the conn is not encrypted)
2: When trying to connect encrypted, SSMS returns an error during the pre-login handshake saying "The certificate's CN name does not match the passed value." When using the same cert in IIS there are no problems whatsoever, and the CN in the cert does indeed match the server name so I'm thinking the real error is hiding beneath the one that is thrown.
Also tried to configure the SQL Client Network Utility to force encryption from the client side, but this didn't seem to have any effect. I tried to use Network Monitor to capture network traffic, but since it appears to be encrypted (SQL encrypts login handshake anyway afaik) I couldn't get much out of it.. Does anyone have any tips?
View 2 Replies
View Related
Apr 30, 2007
Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates what so ever. Am I missing something or is that part of the security to prevent gaining details about certificate usage?
View 5 Replies
View Related
Apr 9, 2008
I have a flat file that contains 20.00- value and I am trying to import it into a decimal field and it does not like the sign. What do I need to do to convert this via SSIS? Thank you
View 1 Replies
View Related
Apr 25, 2008
SQL/SERVER 2000:
Data transform task which copies data from a text file to a db table.
Text file field value = 0000000242E (signed decimal)
DB column data type = decimal(11,2)
How do I get this value correctly converted? Getting "invalid data value" error message.
thanks for any help
View 1 Replies
View Related
Jul 30, 2007
Hi,
I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.
But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778
So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778
any help will be appreciated.
Regards
Karen
View 2 Replies
View Related
Jul 30, 2007
Hi,
I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.
But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778
So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778
any help will be appreciated.
Regards
Karen
View 7 Replies
View Related
Oct 28, 2015
I have a requirement to load multiple flat files in target table .
I have created the package which used to load files into target table using For each loop container.
But now requirement has been changed now I have to take only those files from table where status="Success" and max JobId. By the query I am to get those records which need to load into table.
Below query I am using to get the files which need to load.
select [JobLogKey],[SrcNm],[DestNm]
FROM [ConfigRep].[dbo].[JobLog]
Where [JobId]=
(Select Max(cast([JobId] as Int)) Jobid
FROM [ConfigRep].[dbo].[JobLog]
Where [JobStat]='Success')
Output:-
JobLogKey SrcNm  DestNm
268 H:Data PlatformSource FileClient2LocHGSSpecLocation.txt Location.txt
269 H:Data PlatformSource FileClient1LocHGSSpecLocation.txt Location.txt
I have to load using above 2 files which are under SrcNm. I have created one variable called FileToLoad as Object and mapping to result set of above query. I have create JobId,SrcNm and DestNm variable to catch the record at every loop. I have  created 2 For each Loop containerÂ
Below screen shot of outer Foreach loop. Till here Its working fine. Inner for each loop container not executing any task under that. How to get it done.
View 3 Replies
View Related
May 29, 2015
how do you load the multiple flat files  to into destination dynamically?
View 9 Replies
View Related
Oct 24, 2007
I am getting the following error when trying to load multiple excel files using for each loop container in SSIS, I tried to put the quotes in several different ways but still can't get rid of this error. I was able to successfully load single excel file, but when I use the for each loop container that's when I am having problems. Any help is greatly appreciated. Thx.
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package1 [Connection manager "SourceConnectionExcel"]: The connection string components cannot contain unquoted semicolons. If the value must contain a semicolon, enclose the entire value in quotes. This error occurs when values in the connection string contain unquoted semicolons, such as the InitialCatalog property.
Error at Package1: The result of the expression ""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::Folder] + @[User::file] + ";Extended Properties="Excel 8.0;HDR=NO";"
" on property "ExcelFilePath" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
View 8 Replies
View Related
Apr 29, 2015
I have multiple excel Files  each has one sheet (With same column names) need to be loaded in a single table. I tried For each loop but couldn't succeed. Â
As I am new to SSIS. How to configure For each loop container for this...
View 5 Replies
View Related
Aug 26, 2015
My Requirement ,In Source Database 5 tables are there ( Emp,Loc,dept,Time,Product ), Destination is Single Excel file.But Dynamically how to load each table information to load into each sheet wise through SSIS Package?
View 3 Replies
View Related
Oct 31, 2007
Hi, a silly question here.
I wrote an assembly and attached to a trigger in one of my tables. It works as I expected, but then I decided to modify the code, say adding some new triggers that I will use with other tables. Then I realize that in order to update my new version of the assembly in SQL first I need to remove the trigger in the table, then remove the assembly, add the new version and finally run a script to create the trigger again in the table.
How could I avoid going through all this process everytime I want to update my assembly?
Thank you!
View 1 Replies
View Related
Oct 12, 2006
This is related to one of my previous posts.
I am running a CLR stored proc that goes to an EDS (Novell) server with LDAP and returns records into a SQL table.
I am using the Novell ldap library.
I want to do this with SSL so my code referneces the Mono security library as well.
However when I make the call to the stored proc to run in SSL, I get an object not found error. I do not think that the the Novell assembly can "find" the Mono assembly.
Two points:
1/ I can do the SSL if I run it as an asp.net page (so I know the SSL works)
2/ The proc runs and pulls all the records in non-SSL (so I know the proc works)
Any ideas?
Thanks,
BIG
View 22 Replies
View Related
Jan 19, 2007
i found one tutorial on self signing assemblies for use in sql server, but it appears to have errors. does anyone know of a better tutorial on this topic? the site im currently looking at is here: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
View 1 Replies
View Related
Jan 6, 2008
I want to use a custom assembly in my report expression.
I have created a class library and built it. Then what do I do next.
Can yu be a little more explicit since I am a novice.
Thanks
Sai
View 3 Replies
View Related
May 16, 2005
I have a question: how is it planned to replicate changes made to the assemblies and to the CLR functions or UDT? Will Visual Studio deploy change of the Assembly as ALTER not DROP/CREATE?
View 1 Replies
View Related
Jun 15, 2007
Good morning,
How does SQL Server 2005 handle the replication of assemblies? I get the following when trying to replicate an assembly:
Timestamp: 6/15/2007 9:41:33 AM
Message: HandlingInstanceID: 05eaed53-b6be-42da-8c72-0f91fcbfa5b6
An exception of type 'Microsoft.SqlServer.Replication.ComErrorException' occurred and was caught.
-------------------------------------------------------------------------------------------------
06/15/2007 09:41:33
Type : Microsoft.SqlServer.Replication.ComErrorException, Microsoft.SqlServer.Replication, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Message : The schema script 'StratisDSonetd85d6e89_1061.sch' could not be propagated to the subscriber.
Source : Merge Replication Provider
Help link :
ErrorMessage : Unspecified error
InterfaceId : 00000000-0000-0000-0000-000000000000
HelpFile :
HelpContext : 0
Description : The schema script 'StratisDSonetd85d6e89_1061.sch' could not be propagated to the subscriber.
ComHResult : -2147467259
ErrorCode : -2147467259
Data : System.Collections.ListDictionaryInternal
TargetSite : Void Run()
Stack Trace : at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Run()
at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Synchronize()
at SQLClientSync.SQLClientSync.Replicate()
Additional Info:
MachineName : REPCLIENT
TimeStamp : 6/15/2007 1:41:33 PM
FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=1.1.1.1, Culture=neutral, PublicKeyToken=d2ff10320a77ec18
AppDomainName : sqlclientsync.exe
ThreadIdentity :
WindowsIdentity : REPCLIENTAdministrator
I appears that the initial replication of the assembly was fine, when re-running the sync (no changes to the assembly), this error is generated.
Thanks...
View 3 Replies
View Related
Jun 15, 2015
i have a excel file in which i have a date column it having the below date formats belowÂ
Install Date
20140721
31.07.2014
07.04.2015
20150108
20140811
20150216
7/21/2014
11.08.2014
07.08.2014
So using SSIS how we would load this date column into the table into one format like dd/mm/yyyy or any single date format
View 6 Replies
View Related
Mar 20, 2008
How to add assemblies to Sql server 2005 to do some user defined function......with some example code..........
View 1 Replies
View Related
Oct 10, 2007
Hi all,
I need to dynamically create a ADO.NET connection manger
so, my question is if it is safe to create it with the string that is generated (ConnectionManagerType) when creating the connection manager manually on my PC.
I understood that this string is the full qualified name of the assembly, so will it be different when I create it on another P.C or it is fixed on all P.Cs ?
Thanks ahead
Eran
View 3 Replies
View Related
Apr 12, 2007
I have followed the steps outlined in the knowledge base article http://support.microsoft.com/kb/913668 for effecting Xml Serialization within the SQL CLR. That is, I have
1. Prebuilt the serialization assembly X.Serializers for the types in assembly X and,
2. Registered both assemblies with SQL Server via the create assembly directive
Yet, when I attempt to create an XmlSerializer on the basis of one of the types defined in X, SQL CLR ignores the pre-built serialization assembly and attempts to dynamically create/load the assembly. Since dynamic loading is disallowed, this fails with the expected exception:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(Com
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at Cypress.Services.Client..
What do I need to do the force the runtime to load the pre-built serialization assembly instead of dynamically trying to create one?
Thank You,
Chris.
View 11 Replies
View Related
Jun 1, 2006
Hi,
It's possible to register an assembly in SqlServer 2005 using the
CREATE ASSEMBLY
syntax.
How can I get a list of assemblies that have been registered?
Thanks
Herbjörn
View 1 Replies
View Related
Apr 23, 2006
Where is it documented what the list of approved assemblies are for SQL 2005. Also, is there a way via T-SQL, or other means to get it programatically?
View 4 Replies
View Related
Mar 23, 2008
I have two questions.
How can I run my C++ assembly in safe mode?
I'm building a SQL function from C++ and compile my assembly with /clrafe. After that I create the assembly with SAFE permission sets and create the function, both in SQL. But the server don't accept load or execute the function because of permission error. It says:
Code Snippet
"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65561. The server may be running out of resources, or the assembly may not be trusted with PERMISSIONSET = EXTERNALACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues."
Does the terminology coince with the technology, or is it diffrent "safety" conditions we are speaking about? One for the SQL permission set, and another safety for CLR type-safe assemblies?
Anyway, after playing around a bit, and following the tricks from this thread. I managed to load and execute the function, but only in UNSAFE permission (unrestricted mode?)
---8<----
My second question is can I use unrestricted mode for assembly compiled with /clr (mixed CLR)?
I'm asking, because I have some C source code that I want to reuse, and for this I can't be type-safe and therefore need to compile only with /clr (mixed CLR).
Should I go back to the plain C API in SQL 2000 to implement such function in SQL 2005, and ignore all new things in c++/CLR/.NET?
If you have any points to C++ sample code, then please forward this to me.
View 4 Replies
View Related
Apr 12, 2007
I have followed the steps outlined in the knowledge base article http://support.microsoft.com/kb/913668 for effecting Xml Serialization within the SQL CLR. That is, I have
1. Prebuilt the serialization assembly X.Serializers for the types in assembly X via the SGEN tool and,
2. Registered both assemblies with SQL Server via the create assembly directive
Yet, when I attempt to create an XmlSerializer on the basis of one of the types defined in X, SQL CLR ignores the pre-built serialization assembly and attempts to dynamically create/load the assembly. Since dynamic loading is disallowed, this fails with the expected exception:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(Com
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at Cypress.Services.Client..
What do I need to do the force the runtime to load the pre-built serialization assembly instead of dynamically trying to create one?
Thank You,
Chris.
View 1 Replies
View Related
Jul 11, 2007
Where are CLR Assemblies stored on SQL Server when compiled from a SQL Server Project?
Are they stored in a specific folder or stored inside the database?
Does deleting the Assembly under Programmability/Assemblies remove the Assembly from the server?
Thanks.
View 6 Replies
View Related
Dec 14, 2007
Hi,
Here I will describe my problem.
1. We are loading large amount of data from database on background thread which is starting on Application_start event in global.aspx.cs file.The data is later cached for subsquent request to improve the performance.
2. Now when we put the application on web farm garden, it is not able to load the application.
3. We are sending the request the servers through Router kind of application.
4 This application is working fine on single server enviornment.
Please help us.
Ajay Kumar Dwivedi
View 1 Replies
View Related