The Certificate, Asymmetric Key, Or Private Key File Does Not Exist Or Has Invalid Format.
Sep 20, 2006
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'
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 set up Service broker (2008R2 -> 2012) across different servers and domains using certificates.I set up 2 queues (one for sending, one for receiving).I set up 6 services (3 for send, 3 for receive), all 3 sharing the same queue type. (srv_send_1, srv_send_2, srv_send_3), (srv_receive_1, srv_receive_2, srv_receive_3).I set up a route for each receiving service on the source, and for each sending service on the target.I set up a SB binding to the remote for each sender service type.I granted send rights to the sending services on the source server.
I then tested my first service, and it worked perfectly (still works).My second service however is failing with the error "The certificate's private key cannot be found".I am stumped, as all the setup code was a lot of copy/paste which I have checked, rechecked and even re-coded.Basically, here is what Im doing:
begin dialog @ConversationHandle FROM Service srv_send_1 TO Service 'srv_receive_1' ON CONTRACT myContract WITH ENCRYPTION = OFF, LIFETIME = 60*60*24*7; SEND ON CONVERSATION @conversationHandle MESSAGE TYPE [my_msgtype] (@xml);
The above code works 100% with service1, but not with service2.Note that on profiler, the target server doesnt receive any communication. The error shows on the sender profiler.Also, The conversation shows up as CONVERSING in sys.conversation_endpoints and the entry exists in sys.transmission_queue.
We are looking to put column level encryption on a table. Great! Wonderful! Look in books online and see that you must give "control" perm to the Certificate for any user that needs to use the symmetric key.
Now my question. I have backed up the Certificate to file. What I have found playing around with the commands is it is possible to run the following command:
Alter Certificate MyCert Remove private key
BOL says their is no "Restore Certificate" command and to just use "Create Certificate". I have tried this and I keep getting an error saying the certificate already exist in the database.
What have I missed? How can I restore this certificate without restoring the ENTIRE database? Is there a permission set that can be applied to the Certificate to stop thsi kind of command from being run while still having the symmetric key usable? Any help would be of great use!
Thanks,
*note* this is using the MasterKey->Cert->SymmetricKey
My colleague is working on bulk insert task from SSIS and since the data file does not contain any valid delimeter one of the suggestion he got is to use a file format to address the issue. Thus a bcp command is used to generate the format file, as per below.
The file file format was generated, from the data flow we added the BULK INSERT task and set the properties accordingly including the File Format and location of the file. Upon running the task itself we encountered the error as per below.
[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load. The file "C:HFISTAT.fmt" does not exist.".
Progress: The Bulk Insert task is completed. - 100 percent complete
Task Bulk Insert Task failed
Have checked the file and it is in C: drive and it is not protected or read-only. Validated the output file and it is as per expected. Any help would be appreciated very much.
When creating xml fileformat its throwing me error "invalid ordinal".
When created non-xml file format, no error, and was also able to load data file into sql table. Not sure why bcp (Version: 10.50.1600.1) is not able to create xml file format.
C:>BCP "MyGDB.dbo.Items_Import" format nul -f"C:AnkitTempBCPItemsMaster.xml" -x -w -T -S"(Local)"
SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid ordinal for field 2 in xml format file.
I try to import data with bulk insert. Here is my table:
CREATE TABLE [data].[example]( Â col1 [varchar](10) NOT NULL, Â col2 [datetime] NOT NULL, Â col3 [date] NOT NULL, Â col4 [varchar](6) NOT NULL, Â col5 [varchar](3) NOT NULL,
[Code] ....
My format file:
10.0 7 1Â SQLCHAR 0 Â 10Â "@|@"Â 2Â Col2Â "" 1Â SQLCHAR 0 Â 10Â "@|@"Â 3Â Col3Â "" 2Â SQLCHAR 0 Â 6Â "@|@"Â 4Â Col4Â Latin1_General_CI_AS
[Code] .....
The first column should store double (in col2 and col3) in my table
My file: Col1,Col2,Col3,Col4,Col5,Col6,Col7 2015-04-30@|@MDDS@|@ADP@|@EUR@|@185.630624@|@2015-04-30@|@MDDS 2015-04-30@|@MDDS@|@AED@|@EUR@|@4.107276@|@2015-04-30@|@MDDS
My command: bulk insert data.example from 'R:epoolexample.csv' WITH(FORMATFILE = 'R:cfgexample.fmt' , FIRSTROW = 2)
Get error: Msg 4823, Level 16, State 1, Line 2 Cannot bulk load. Invalid column number in the format file "R:cfgexample.fmt".
I changed some things as: used ";" and "," as column delimiter changed file type from UNIX to DOS and adjusted the format file with " " for row delimiter
Removed this line from format file 1Â SQLCHAR 0 Â 10Â "@|@"Â 2Â Col2Â "" Nothing works ....
I have an appplication which creates triggers on tables. It works on nearly every table I have in my repository except for one. When I attempt to apply it to that one, I get the following:
"The object TABLENAME does not exist or is invalid for this operation."
I know the table exists and am able to access data in it just fine. So what are all possible reasons why CREATE TRIGGER might be an invalid operation for it?
I am trying to convert a certificate that was exported from our database server to be used by SQL Server for database encryption. When I run the PVKConverter, not Private Key File (PVK) is generated.The certificate has Server and Client Authentication as the purposes of the certificate.Â
What purpose or purposes does the certificate need in order to be able to be used by SQL Server 2012 SP2?Why doesn't the PVKConverter generate a private key file?I can use the command makecerts to generate a self signed certificate and have it work with SQL Server database encryption.
I am looking at documentation of CREATE CERTIFICATE statement. I am having hard time in understanding if I want to create CERTIFICATE with above mentioned options, how I am supposed to create either PRIVATE KEY file or EXECUTABLE file. any example would be really helpful for what I am doing here.
I have created a certificate and now have taken a backup of this file by the following code:
backup certificate EncrProdCode to file = 'C:BackupCertEncrBackup.cer' with private key(file = 'C:BackupCertEncrPrivKeyBackup.pvk', encryption by password = 'EncrPrivKeyBackuppwd')
Now i want to create a certificate IN DIFFERENT DATABASE from this file. i m using the following database:
create certificate EncrFromFile from file = 'C:BackupCertEncrBackup.cer' with private key(file = 'C:BackupCertEncrPrivKeyBackup.pvk', decryption by password = 'EncrPrivKeyBackuppwd')
But is showing error:
Msg 15232, Level 16, State 1, Line 1 A certificate with name 'EncrFromFile' already exists or this certificate already has been added to the database.
No certificate with this name exists. why i m getting this error. and how can use the same certificate in other database. Is there any other way to create a certificate from an existing certificate file.
In SQL Server 2000 we are loading data into a smalldatetime field. The file is being loaded from a text file that contains the date in the following format: 07/24/2000 16:08
The problem occurs when the data is as follows: 04/28/2000 14:60
Microsoft SQL Server6.5 was forgiving and would translate this to 15:00 upon load but SQL Server2000 is failing with the Invalid Time Format error.
Can anything be done to force SQL Server 2000 to recognize this?
I am exporting data from Oracle to SQL server using SSIS package, I see some invalid date in the orcale tables. I am trying to redirecting them to different output table. But I am getting the following error.
Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid date format".
Can any one know how to filter these invalid values?
I'm using the sample on custom authentication from msdn but whenever I try to login I get this message.What URI are they complaining about? Has it anything to do with the custom cookie handling? I cant for the world understand whats wrong.
I'm using ODBC (System DSN) to connect to SQL Server 7.0 Database. Whenever I try to connect to SQL Server Database from my client application, I'm getting the following error message -
---------------------------------------------------------- Microsoft SQL Server has reported the following error:
[Microsoft] [ODBC SQL Server Driver] Invalid Date format ----------------------------------------------------------
After this message, I'm getting another error message -
I'm calculating matrix including totals in the database. I would like to set a background color for all total cells. For total cells with now dat in database I fail to put codiotional formating. I use the following code: =iif(Fields!Col1Desc.Value="Total", "#60759b", "Transparent") For cells under "Total" columns that do not got any db value I got color "Transparent" and not "#60759b"
How do I recognize to which column or row a cell belong in a matrix even if it not a value that come from db but empty one?
for example, note the background color of the cell of Male, Total and 2006
NOONE ASKED FOR THIS!!! why the heck is BACKUP CERTIFICATE altering the file's ACLs - it's just a PUBLIC CERT I'm exporting!!
in additions MS doesn't seem able to add the ability to re-inherit file-rights from parent objects via the command-shell.
I export the public-key for a reason! I want to import it on another machine (automatically) to establish a trust. But this freaking "feature" is costing me HOURS of research on how to get rid of these ACLs on a public certificate!!!
argh!!!
sorry, I really had to get this out!
why not at least give us a switch we can set in BACKUP CERTIFICATE command to stop this from happening?
is there a work-around? now I need to activate EXEC sp_configure 'show advanced options', 1 and EXEC sp_configure 'xp_cmdshell', 1 just to be able to issue a (not working) DOS command cacls... to try to fix the "feature".
there's got to be a better way!!!
also - why can't there be a switch to force overwriting the target file, if it already exists? It's a BIG PAIN having to do lots of other stuff, just to be able to "backup" the certificate!
You gave us "xp_FileExist", but no xp_FileDelete with similar parameters!! again, I need to issue DOS COMMANDS just to do stuff I would not have to if someone at the levers was actually activating the back-brain!
PLEASE give me some hints on how MS recommends to solve these features!
Hi, I am trying to do a simple ftp using DTS. I am using a Dynamic properties task as the file name is a bit complicated eg - f2368fd1.ext.040330.2 where 040330 is according to the date and the .2 is the second file for the day. When i run the task, it fails giving me the error "Invalid format for string source names"
I then tried to use a simple file name such as TD.txt as a constant in the Dynamic properties task but still it fails. Any ideas ??
We have a SSIS package which is accessing a remote Windows file share location. The package first moves the file from folder-1 to folder-2 and also renames the file during this process. Then the package reads the file (using a flat file connection FF_SRC) from folder-2 and renames it again after processing it successfully.
The permissions given to the user executing the package on folder-2 are: Read+Write+Modify+List folder contents.
We are facing an error:
Code SnippetFile or directory "Z:folder-2XYZ.txt" represented by connection "FF_SRC" does not exist.
We are getting the above error when the SSIS package is trying to rename the file the second time in folder-2.
However, the file exists in folder-2.
The OS is Windows 2000 Server SP4.
Any ideas why this could be happening and how it could be resolved?
Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.
Following script runs fine on SQL Server.
Code Snippet
use master
Create Master Key Encryption BY Password = '45Gme*3^&fwu';
BACKUP MASTER KEY TO FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk'
ENCRYPTION BY PASSWORD = '45Gme*3^&fwu'
Create Certificate EndPointCertificateC
WITH Subject = 'C.Server.Local',
START_DATE = '06/01/2006',
EXPIRY_DATE = '01/01/2008'
ACTIVE FOR BEGIN_DIALOG = ON;
BACKUP CERTIFICATE EndPointCertificateC
TO FILE = 'C:ServiceBrokerEndPointCertificateC.cer'
Following script runs on SQL Server Express:
Code Snippet
Create Certificate EndPointCertificateC
From FILE = 'C:ServiceBrokerEndPointCertificateC.cer'
WITH PRIVATE KEY (
FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk',
DECRYPTION BY PASSWORD = '45Gme*3^&fwu'
);
If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong.
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
Hello All,I am getting the following error when attemping to open a table inSQL2kSP3a.________________________________________SQL Server Enterprise ManagerDatabase Server: Microsoft SQL ServerVersion: 08.00.0760Runtime Error: [Microsoft][ODBC SQL Server Driver]Invalid time format_________________________________________I cannot find it in sysmessages, or on the web.Any ideas about how to resolve this? And how it occured...Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
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
Hi, 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 master2 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 TestLogin6 GO7 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.
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';
1)My source files[.txt] come from an FTP server.I need to find a way to include within my SSIS package,a task that would watch a certain folder of the FTP Server and send a mail notification as soon as the file arrives.Tried using filewatcher task http://www.sqlis.com/23.aspx, but the task stays "yellow" for a long time while package is being excuted.I dont know if this is what I need to use.Are they any other task/ways?
Another Problem I'm facing is;
2)If the source file coming in the FTP Server is zipped.How do I unzip the file and move it back to the FTP Server folder for the task in 1) to watch on it .Any ideas ?
i have a data flow task which create a .txt file in destination from source table. i have used expression for the out file name to add date values to the out put file name using expression window
for example the output file is department05032008, my biggest problem over here at this moment is i need to suffix the file name if the file executes more then one time. ( requirment is i cannot use timestamp)
for example today my output file name is department05032008, the next hour the package executes it the file name should be department05032008A, then department05032008B and so on .
can i suffix the file name in expression window where i am generating the output file name , if possible i want to avoid the vb script as i am not good at it.
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
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;
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."
