I am trying to create an asymmetric key to have EXTERNAL ACCESS ASSEMBLY for an SQL login. When I try to run following script. it gives error
1 User master
2 GO
3 CREATE ASYMMETRIC KEY SN FROM EXECUTABLE FILE = 'D:Partners.dll'
4 CREATE LOGIN TestLogin FROM ASYMMETRIC KEY SN
5 GRANT EXTERNAL ACCESS ASSEMBLY TO TestLogin
6 GO
7
Error is "The certificate, asymmetric key, or private key file does not exist or has invalid format."
Any help how can I fix this error. The database already have Trustworth ON.
Hi I created an sqlserverproject successfuly ( just has one CLR stored proc) (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/mandataaccess.asp) i followed above steps to create the project. when i tried to deploy the same to sql server VS studio threw below error CREATE ASSEMBLY for assembly 'MySqlServerProject' failed because assembly 'MySqlServerProject' 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. MySqlServerProject then i realized i will have to either sign the assembly or create an asymmetric key. i decied to follow the latter. so i tried below t-sql statements in sql sever 2005 use master GO CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:myWorkSQL2005DBProMySqlServerProjectMySqlServerProjectinDebugMySqlServerProject.dll' when i execute the above, SQL Server threw below error The certificate, asymmetric key, or private key file does not exist or has invalid format. What i am i doing wrong. please advise THNQDigital
I'm trying to create assembly with "PERMISSION_SET = UNSAFE". For that I've signed assembly's .dll and installed root certificate to €œTrusted Root Certificate Authority.€?: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx now I'm trying to create login from asymmetric key:
USE master GO
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:Documents and SettingsAll UsersDocumentshunterStoredProcedures.dll' CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey
but I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."
How to backup asymmetric key in SQL 2005 created in the following way so it can be copied to another server ? Also can you copy it to the other server after backing it up.
CREATE ASYMMETRIC KEY ccnumber WITH ALGORITHM = RSA_512 ENCRYPTION BY PASSWORD = 'password';
i've getting ready to implement encryption on a rather large database. I'd read that if performance is of utmost concert, you should use symmetric keys. I want to encrypt those keys by asymmetric keys. My code is working, but i'm just not sure if there is a quicker way? do you have to open and close the key each time you select/update/insert in a stored procedure that references an encrypted column, or is there a way to just modify the code by adding the encryptbykey/decryptbykey functions?
has anyone implemented encryption on columns in large tables? any suggestions for me?
Thanks, Pete
here's my code to create the keys:
create asymmetric key ASK_Auto_Encrypt with algorithm = RSA_512;
create symmetric key SK_AE with algorithm = TRIPLE_DES encryption by asymmetric key ASK_Auto_Encrypt;
here's my code to test this:
create table encryption_test (test varchar(50));
open symmetric key SK_AE decryption by asymmetric key ASK_Auto_Encrypt;
insert into encryption_test select encryptbykey(key_guid('SK_AE'),'test');
select convert(varchar(max),decryptbykey(test)) from encryption_test;
We have a curious situation on a SQL 2014 DB, with Trustworthy set to OFF. There is a job that runs a data export to a file via a CLR. The assembly as PERMISSION_SET = EXTERNAL_ACCESS, however there is no Asymmetric key for the assembly. Therefore what I trying to work out is why this is NOT failing. Some further information on this specific database that may or may not be relevant is:
1. It was upgraded a few weeks ago (Backup/Restore) from a SQL 2012 - SQL 2014 server 2. It as a Compatibility Level = 110 (2012) 3. The Previous 2012 database DID have Trustworthy ON 4. The CLR are actually being run against a snapshot of the database (Actually I think this one is a red herring. The SP is getting data from a table in the snapshot, but the CLR used it the one from the main DB)
We are planning to encrypt few fields using asymmetric encryption. Tyring share public key with users and retain private key with us. How to generate keys? Haven't found any solid document on how to generate these keys.
I am sure I'm being dumb here but I am trying to deploy an assembly with external_access.
I have signed the assembly using the <new> option in the project properties.
When I then try and create the Key I get the above error using the code below.
CREATE ASYMMETRIC KEY SQLExtensionUDTKey
FROM EXECUTABLE FILE = 'C:Documents and SettingsSimon SabinMy DocumentsVisual Studio 2005ProjectsSQLBitsCoreSQLExtensionsSQLExtensions.UDTinDebugSQLExtensions.UDT.dll'
I wanted to check the asymmetric key option in sql 2005. I copied the books online code for creating a asymmetric key and then used this key for creating a login. Now when I try to login without entering any password I am receiving 18456 error. I would like to know what I am missing here. If I use CREATE LOGIN from asymmetric key or certificate how do I login and with what credentials. Do I need to provide any password.CREATE ASYMMETRIC KEY PacificSales09 WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = 'bmsA$dk7i82bv55foajsd9764'; GO
CREATE LOGIN asm FROM ASYMMETRIC KEY PacificSales09;
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.
I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.
Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance
Just a quick one. I have some create scripts that were created for mysql and i am wanting to convert them to a
SQL Server create script. I am just wondering if anyone knows of any utilities or codeplex projects, etc that can perform the convert process.
The script is fairly basic but it is not fully compatible but i have a lot of them thus why i want to automate the process. The majority of the script is just table create statements like the following:
Code Snippet -- ----------------------------------------------------- -- Table `dbo`.`Staff` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `dbo`.`Staff` ( `StaffId` INT NOT NULL AUTO_INCREMENT , `IndividualId` INT NOT NULL DEFAULT 0 , `PositionId` INT NOT NULL DEFAULT 0 , `EmploymentStartDate` DATETIME NULL , `EmploymentEndDate` DATETIME NULL , `SupervisorStaffId` INT NULL , `TerminatedEmploymentReasonId` INT NULL , `DeletedFl` BIT NOT NULL , `CreateDateTime` DATETIME NOT NULL , `CreateUserId` INT NOT NULL , `ChangeUserId` INT NOT NULL , `ChangeDateTime` DATETIME NOT NULL , `VersionNum` INT NOT NULL , PRIMARY KEY (`StaffId`) , INDEX Individual_Staff_IndividualId_Ref (`IndividualId` ASC) , INDEX Lookup_Staff_PositionId (`PositionId` ASC) , INDEX Staff_Staff_SupervisorStaffId_Ref (`SupervisorStaffId` ASC) , INDEX Lookup_Staff_TerminatedEmploymentReasonId (`TerminatedEmploymentReasonId` ASC) , CONSTRAINT `Individual_Staff_IndividualId_Ref` FOREIGN KEY (`IndividualId` ) REFERENCES `dbo`.`Individual` (`IndividualId` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Lookup_Staff_PositionId` FOREIGN KEY (`PositionId` ) REFERENCES `dbo`.`Lookup` (`LookupId` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Staff_Staff_SupervisorStaffId_Ref` FOREIGN KEY (`SupervisorStaffId` ) REFERENCES `dbo`.`Staff` (`StaffId` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Lookup_Staff_TerminatedEmploymentReasonId` FOREIGN KEY (`TerminatedEmploymentReasonId` ) REFERENCES `dbo`.`Lookup` (`LookupId` ) ON DELETE NO ACTION ON UPDATE NO ACTION);
Thanks for the help
Anthony
Note: i know if you have a mysql database you can go from one to the other, but i specifically need to take these mysql create scripts and convert them to sql server create scripts and there are a LOT of them i.e. more than 1000 script files that each contains a couple of hundred table create statements.
I've tried those two operations in the Management Studio. Though we can create a mining structure and mining model in Management Studion, but we cannot process the analysis-service database.
(1) I create only a mining structure through CREATE MINING STRUCTURE. No error reported. But if I process the analysis-service database in Management Studio I always get error
'Error : The '<mining_structure__name>' structure does not contain bindings to data (or contain bindings that are not valid) and cannot be processed.
I then tried to create it by creating and running an XMLA script. It was successful. However, it's much harder to learn XMLA.
If any of you created an analysis-service database in Mgt Studio, and create a mining structure in the same place using DMX script, can you process the database?
(2) Is there any use of CREATE MINING STRUCTURE operation without binding to any table? Examples I saw so far did not show relating it to do. In my experience processing the analysis-service database with that mining structure is doomed to fail.
(3) Is there any way we can create mining structure through CREATE MINING
STRUCTURE operation in Management Studio and use RELATED TO clause
to bind it to any Relationship to an attribute column (mandatory only if it applies), indicated by the RELATED TO clause
(4) If this is the fact, is there any use of CREATE MINING STRUCTURE operation? If we use BI Dev Studio, it's much easier to use the wizard.
(5) I found I cannot create a mining model inside a mining structure through operation CREATE MINING MODEL. If you call that operation, you end up having a mining model and a mining structure with the same name. I found that in order to create a mining model inside a mining structure you have to call operation ALTER MINING STRUCTURE ADD MINING MODEL. Is it true this is the only way?
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
i need to create a dbo in new databases that i made. i sent them to another SQL server and they do not have any users and especially do not have a dbo as one of the users. i really would like to have detailed directions on this. i know that i've done it before, but i'm beggining to think that it might have been just by accident. thanks!
Greetings, Using a server base database called Respond (fe) and Sql server 2000 as a (be), I was trying to create a new db. Instead of designing from scratch, I was thinking to copy an existing Respond db, delete existing data, and modify it to fit my new requirements. I'm new to sql server db and would like to know 1. How to delete data from the table (be). 2. How to perform an automatic backup daily (or may be every other day) and how to check if the backup is doing what it was suppose to do in sql server 2000.
i want to give to my user permission to create job , add step to job etc. i can't do this when my user do not use sa user but i don't want to give him the sa password so how can i do this eytanb@mekorot.co.il
