import OpenSSL certificate with private key into sql server 2005 express edtion

hey,

i have a problem, to import a self signed openssl certificate into the sql server 2005.

my final idea is to get encrypted columns from the database over an jdbc connection in a java client.

when i use a certificate generated by the sql server 2005, i can encryt columns of a table. then i catch the

the result in my java client. but in java, i need a keystore with the private key of the certificate.

ok. i have export the the certificate and the private key of the sql server 2005.

problem: in a keystore i can only import the certificate (signed public key) but not the private key.

my new idea is to import an openssl certificate or an certificate generated by the keytool (java) into the

sql server 2005 and encypt the data with the imported certificate. Problem: The SQL Server give me an

Exception: (Sorry i drag & drop the exception, is written in german)

Msg 15208, Level 16, State 1, Line 2

Die Datei für das Zertifikat, den asymmetrischen Schlüssel oder den privaten Schlüssel ist nicht vorhanden oder weist ein ungültiges Format auf.

My Import Statement is:

CREATE CERTIFICATE InsuranceCertOpenSSL

FROM

FILE = 'E:\master\keys\insuranceservice_tomcat_apr_x509_certificate_with_cygwin_openssl_20060630\insuranceservice_tomcat_x509_certificate_with_openssl.crt'

WITH PRIVATE KEY (

FILE = 'E:\master\keys\insuranceservice_tomcat_apr_x509_certificate_with_cygwin_openssl_20060630\insuranceservice_tomcat_x509_certificate_with_openssl_private.key',

DECRYPTION BY PASSWORD = 'testit2_',

ENCRYPTION BY PASSWORD = 'testit2_'

)

I use password encryption, and not the internal master key (or service master key)

Hope for help :)

nils

Hi Nils,

Could you try creating a certificate in keytool with a private key that is not encrypted? Then when you create it in SQL Server, you can omit the "DECRYPTION BY PASSWORD..." line.

It may simply be that the decryption of the certificate is failing.

Sung

|||

HI Sung,

thank you for answering.

i create a test certificate with private key in openssl and with keytool too.

i bring the certificate an the private key outside the sql server 2005 in the DER Format, because i read

that the certificate in SQL Server use this format.

OK.

i can import the certificate from openssl. (without the WITH PRIVATE KEY option).

CREATE CERTIFICATE InsuranceCertOpenSSL
FROM
FILE = 'E:\CertSqlServer2005InsuranceDatabaseOpenSSL.crt'

it work. but when i encrypt and decrypt somthing the sql server have no private key.

SELECT Cert_ID('InsuranceCertOenSSL')

DECLARE @.NAMEENC NVARCHAR(100)
DECLARE @.PWD NVARCHAR(100)
SELECT @.PWD = 'testit2_'
SELECT @.NAMEENC = EncryptByCert(Cert_ID('InsuranceCertOpenSSL'),N'O12345678901234MMM5678XXX901234567890 O12345678901234567')
SELECT @.NAMEENC as encode
SELECT CAST(DecryptByCert(Cert_ID('InsuranceCertOpenSSL'),@.NAMEENC, @.PWD) AS NVARCHAR(MAX)) as decode
GO

Result is null.

OK:

When i import the certificate with an private key.

CREATE CERTIFICATE InsuranceCertOpenSSL
FROM
FILE = 'E:\insuranceservice_tomcat_x509_certificate_with_openssl_DER.crt'
WITH PRIVATE KEY (
FILE = 'E:\insuranceservice_tomcat_x509_certificate_with_openssl_private_DER.key',
DECRYPTION BY PASSWORD = 'testit2_',
ENCRYPTION BY PASSWORD = 'testit2_'
)

it doesn' t work.

my problem is that the sql server 2005 enrcypt the private key by export. also the sql server 2005 import only a encrypted private key.

when the key is encrypted i can not convert into PEM. (with openssl)

My destination is to have encrpted data in my java application. and there i will decrypt.

two ways...

1.) import an expernal generated certificate and private key. this must be imported into the sql server. PROBLEM: can not import the private key (must by encrypted, i think so)

or

2.) create an certificate in sql server 2005 with private key. export both. (DER-Format). To convert the certificate into PEM is no problem. but to convert the private key into pem, openssl give me an exception:

$ openssl rsa -inform DER -in CertSqlServer2005InsuranceDatabase_private.key -o
ut CertSqlServer2005InsuranceDatabase_private_PEM.key -outform PEM
unable to load Private Key
6944:error:0D07207B:asn1 encoding routines:ASN1_get_object:header too long:asn1_
lib.c:150:

I will map this to import the certificate and the key into an keystore and opened in java.

3.) a new idea: encrpt by a symmetric key. but these keys can not be exported from the database. (but recreate in the database)

in the moment i try to create a equivalent symmetric key with openssl .

i don't know the best way. but i need one way. please help

thank you

nils

|||

I looked through the keytool documentation and it does not seem to support the private key file format that SQL Server expects.

How exactly did you create the private key files that you were trying to import in SQL Server?

You may want to use another tool to generate your certificate. Have you tried using makecert to generate the certificates?

Thanks
Laurentiu

|||

Hi Nils,

[Edit - see Laurentiu's comment above]

We do recommend using symmetric keys for encryption as the performance gain is significant, but you are right that they currently cannot be exported.

Do you have to do decryption on both the application and the database? It kind of sounds like you want the database to be able to encrypt and then your application will handle the decryption? If this is the case, you can possibly get around this by storing the full certificate (public/private key pair) with the application and storing only the public key of the ceriticate on the database. You should not need the private key to encrypt data, you only need the private key to decrypt.

Hope this helps,

Sung

|||

Hi Sung,

thanks for your help.

yes you are right. i create an application with web services an database connects. in the application will have the chance to distribute my "moduls". i know i can you ssl. in some parts i use it. but to connect to an database i dont't know to connect to the database over ssl. ok, i can programm a Listener-Thread on the database site, but for many connection i need a connection pool. this is not what i want.

you are right, i can create an external certificate/private key and use it to encrpt and decrypt outside the database. and the database encrypt only data. (i must think about, for a solution that i realy need the decryption in the database. i can encrypt only data-column, and not the searching-keys, and so on.)

soloution 1 (your):

create external certificate with openssl (for example) and encrypt on in the database with the imported certificate (without the private key).

solution 2:

create a symmetirc key in the application and the same in the database:

2.a.) in the database

CREATE SYMMETRIC KEY InsuranceSymDESKey
WITH
ALGORITHM = DES,
KEY_SOURCE = 'testit2_',
IDENTITY_VALUE = 'id_insurance_service_center'
ENCRYPTION BY CERTIFICATE InsuranceCert
go

2.b.) with openssl

openssl des -out insurance_sql_server_symmetric_key_openssl.key - ....

sorry i don't know the right syntax, to generated the same key. is this possible?

nils

|||

HI Cristofor,

>> I looked through the keytool documentation and it does not seem to support the private key file format that SQL Server expects.

The exported SQL Server Certificates in DER-Format. With

openssl x509 -in CertSqlServer2005InsuranceDatabase.crt -inform DER -out CertSqlServer2005InsuranceDatabase_PEM.crt -outform PEM
you can transform the certificate. Is that realy a differnet format for the primary key or is the exported key from the sql server only additional encrypted? In the sql server documentation is only reported that the key is encrypted, but no information about the algorithm or so.

>> How exactly did you create the private key files that you were trying to import in SQL Server?

1.) generate key

$ openssl genrsa -out insuranceservice_tomcat_x509_certificate_with_openssl_private.key -des3 2048

2.) create request

$ openssl req -new -in insuranceservice_tomcat_x509_certificate_with_openssl_private.key -out insuranceservice_tomcat_x509_certificate_with_openssl.csr

3.) create certificate

$ openssl x509 -in insuranceservice_tomcat_x509_certificate_with_openssl.csr -out insuranceservice_tomcat_x509_certificate_with_openssl.crt -req -signkey insuranceservice_tomcat_x509_certificate_with_openssl_private.key -days 365

4.) map private key from PEM into DER

$ openssl rsa -inform PEM -in insuranceservice_tomcat_x509_certificate_with_openssl_private.key -outform DER -out insuranceservice_tomcat_x509_certificate_with_openssl_private_DER.key -des3

5.) map crt from PEM into DER

$ openssl x509 -in insuranceservice_tomcat_x509_certificate_with_openssl.crt -inform PEM -out insuranceservice_tomcat_x509_certificate_with_openssl_DER.crt -outform DER

IMPORT into the sql server:

1.) (works, but without the private key)

CREATE CERTIFICATE InsuranceCertOpenSSL
FROM
FILE = 'E:\insuranceservice_tomcat_x509_certificate_with_openssl_DER.crt'

2.) doesn't work

CREATE CERTIFICATE InsuranceCertOpenSSL
FROM
FILE = 'E:\insuranceservice_tomcat_x509_certificate_with_openssl_DER.crt'
WITH PRIVATE KEY (
FILE = 'E:\insuranceservice_tomcat_x509_certificate_with_openssl_private_DER.key',
DECRYPTION BY PASSWORD = 'testit2_',
ENCRYPTION BY PASSWORD = 'testit2_'
)

When i remove the decryption, it doesn't work also.

>> You may want to use another tool to generate your certificate. Have you tried using makecert to generate the certificates?

i don't know the tool. but often the tools using in the background the openssl. You think this tool make another format the the sql server can understand (only the private key is needed).

i don't know the best way. but i will only send encrypted data over the network.

On which site i create the certificate (openssl or sql server) an then import and use each other is not imported.

also is not imported which tool i use to create the certificate/private key. i use all what give me an solution.

The problems are:

1.) import an certificate and the private key into the sql server 2005, generates by another software like the sql server 2005

2.) use the exported certificate and the private key from a sql server 2005.

In an microsoft document (http://www.microsoft.com/germany/technet/itsolutions/msit/security/sqldatsec.mspx) (german) i found in the last paragraph an information, that only certificates and private key can exchange between two or more sql server 2005. is this right?

Nils

|||

Hey Nils,

In regards to the connection problem, I will have to research that further.

I was trying to figure out a good solution for your second approach and I ran across two problems:

1) SQL Server and OpenSSL are probably generating the keys differently, therefore the keys created in 2a and 2b probably won't work with each other

2) I was talking about this with Raul and he mentioned that beyond that, the encrypted data is stored with extra headers and other info so it's not easy to extract the raw cipher text.

I'm not sure how to get around these two issues. The other alternative is to use JCE to handle encryption and decryption completely externally?

Sung

|||

It seems to me like you are trying to protect the data in transit, not only at rest. I strongly recommend against using the secret storage features in SQL Server 2005 for data in transit protection, instead, I would recommend using SSL between you server and client. You can find more information on this topic in MSDN (Encrypting Connections to SQL Server http://msdn2.microsoft.com/en-us/library/ms189067.aspx).

If my assumption is incorrect and/or you still need to be able to encrypt data in both your client application and SQL Server your best option would be to use certificates generated by makecert as Laurentiu mentioned.

Unfortunately the OpenSSL format for private keys and PVK are not compatible, but you can generate the certificate (public CER file and private key in PVK) using makecert, then to use it on OpenSSL you can use pvk2pfx tool to generate a PFX, as far as I remember, OpenSSL should be able to use DER encoded PFX files.

For the second solution you mentioned, creating the same symmetric key outside SQL Server. While in theory it is possible (KEY_SOURCE uses CAPI CryptDeriveKey to generate a 3DES key), you will face with the SQL Server specific headers that are not properly part of the ciphertext as well as the encrypted header once you are able to decrypt the data. I would strongly recommend against this approach.

I also include a link to a different discussion in the forum with a similar scenario (using an asymmetric key in a client), but in this case the client was using .NET code; while not exactly your scenario, it may be useful.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384472&SiteID=1

I hope this information was useful,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Nils,

makecert is a Microsoft certificate creation utility. It can generate certificates in the format that SQL Server 2005 understands. You can convert those certificates to other formats, such as PFX, using other tools (see http://www.kinook.com/blog/?p=10). I think openssl should know how to read PFX files, but I amy not familiar with openssl or keytool. My suggestion is to generate a certificate using makecert in the format understood by SQL Server, then convert it to PFX and use it with your application.

Thanks
Laurentiu

PS: My name is Laurentiu, Cristofor is my last name.

|||

Thanks to all for help. (and sorry for the mistake with your name laurentiu.)

a.) i use the pvktool to change the header of the certificates. transform the private key.

pvk -in CertSqlServer2005InsuranceDatabase_PVK_private.key -out CertSqlServer2005InsuranceDatabase_PEM_private.key

b.) transform the certificate

openssl x509 -in CertSqlServer2005InsuranceDatabase_DER.crt -inform DER -out CertSqlServer2005InsuranceDatabase_PEM.crt -outform PEM

c.) produce a keystore (pkcs12 format)

openssl pkcs12 -export -in CertSqlServer2005InsuranceDatabase_PEM.crt -inkey CertSqlServer2005InsuranceDatabase_PEM_private.key -out insuranceservice_sql_server_keystore_PKCS12.jks -name insurance_sql_server_cert

d) from java it is easer to work with jks-format keystore

set ksFileIn=insuranceservice_sql_server_keystore_PKCS12.jks
set ksFileOut=insuranceservice_sql_server_keystore_JKS.jks
set keyAlias=insurance_sql_server_cert
set ksPass=testit2_

java testclient.CopyKeyAndCertFrom_PKCS12_To_JKS_KeyStore %ksFileIn% %ksFileOut% %keyAlias% %ksPass%

e.) for this i write a small java programm (testclient.CopyKeyAndCertFrom_PKCS12_To_JKS_KeyStore)

package testclient;

import java.io.*;
import java.util.*;
import java.security.UnrecoverableKeyException;
import java.security.NoSuchAlgorithmException;
import java.security.KeyStoreException;
import java.security.KeyStore;
import java.security.KeyPair;
import java.security.PrivateKey;
import java.security.PublicKey;
import java.security.Key;
import java.security.cert.Certificate;
import java.security.spec.*;

public class CopyKeyAndCertFrom_PKCS12_To_JKS_KeyStore {
public static void main(String[] args) {
try {
System.out.println("Usage: CreateSQLServerKeyStore <pkcs12_keystore_in> <jks_keystore_out>"
+ "<key alias> <keystore password>");

System.out.print("\nstart");


if (args.length != 4) {
System.exit(-1);
}

String ksFileIn = args[0];
String ksFileOutJava = args[1];
String keyAlias = args[2];
char[] ksPass = args[3].toCharArray();

KeyStore ksJKS = KeyStore.getInstance("JKS");
ksJKS.load(null, ksPass);

KeyStore ksPKCS12 = KeyStore.getInstance("PKCS12");
java.io.FileInputStream fis = new FileInputStream(ksFileIn);
ksPKCS12.load(fis, ksPass);

java.security.Key key = null;
java.security.PublicKey publicKey = null;
java.security.cert.Certificate cert = null;
java.security.cert.Certificate[] certChain = new java.security.cert.Certificate[1];

key = ksPKCS12.getKey(keyAlias, ksPass);
if (key instanceof java.security.PrivateKey) {
cert = ksPKCS12.getCertificate(keyAlias);
certChain = ksPKCS12.getCertificateChain(keyAlias);
publicKey = cert.getPublicKey();
}

ksJKS.setKeyEntry(keyAlias, key, ksPass, certChain);

FileOutputStream fos = new FileOutputStream(ksFileOutJava);
ksJKS.store(fos, ksPass);
fos.close();

System.out.print("\nend");
} catch (Exception e) {
System.out.print(e);
}
}
}

e.) for testing (it works also) (makecert)

makecert -r -cy end -sky exchange -n "CN=Nils" -b 01/01/2006 -e 01/01/2011 -a sha1 -sv CertSqlServer2005InsuranceDatabase_makecert_x509_private_key.pvk CertSqlServer2005InsuranceDatabase_makecert_x509.cer

f.) after this you can import the certificate and the private key into the sql server 2005. in java you can also open the

private key and the certificate from the keystore.

...

FileInputStream fis = null;
KeyStore ks = null;
Key key = null;
PrivateKey privateKey = null;
PublicKey publicKey = null;
Certificate cert = null;
Cipher cipher = null;
String encryptedstring = null;
String decryptedstring = null;
byte[] encryptedtext = null;
byte[] decryptedtext = null;

fis = new FileInputStream(dbKeyStore);
ks = KeyStore.getInstance(dbKeyStoreTyp);
ks.load(fis, dbKeyStorePass);
key = ks.getKey(dbKeyStoreAlias, dbKeyStorePass);
if (key instanceof PrivateKey) {
privateKey = (PrivateKey) key;
cert = ks.getCertificate(dbKeyStoreAlias);
publicKey = cert.getPublicKey();
}

Security.addProvider(new BouncyCastleProvider());
cipher = Cipher.getInstance("RSA/1/NoPadding", "BC");
...

Now i can encrypt an decrpyt. BUT i have another problem.

In the sql server 2005 the DecryptByCert and the EnrcyptByCert Method give as the result the datatype varbinary(128).

ok. But the SQL Server doesn't give me the data. for example i write some sql statements.

drop table dbo.TestSec
go

CREATE TABLE dbo.TestSec
( MandantenVerbindungID nvarchar(5) NOT NULL,
MandantenBezeichnungKurz1 varbinary(128) NOT NULL
);
GO

INSERT into dbo.TestSec (MandantenVerbindungID,MandantenBezeichnungKurz1)
values ('NBIT1', EncryptByCert(Cert_ID('InsuranceCert'), N'NBIT1'))
GO

--(1. First SELECT STATEMENT)

SELECT MandantenVerbindungID,
DecryptByCert(Cert_ID('InsuranceCert'), MandantenBezeichnungKurz1, N'testit2_')
FROM dbo.TestSec

Result:

-- 1.Column: NBIT1
-- 2.Column: 0x4E004200490054003100

--(2. Second SELECT STATEMENT)

Select MandantenVerbindungID, MandantenBezeichnungKurz1 from dbo.TestSec

-- 1.Column: NBIT1
-- 2.Column: 0x6FF1AD39946A3C92E16E035446F66930852CA086BECEE05FE1AD95DD578A8281169DF696E3528580B35C9CCEB7AB
5DBBEE4C7F9FB34F75460A2A0DECAA146FD8F8CF9A258D49C2B1A8012918609F9C4CDF53A1439CEEFF21CCE2076DE
166BB123AD2788D2B8BE3BD1F4E967FFEFC84D839C485596CC8556641387D891224914

--(3. Third SELECT STATEMENT) (ONLY for Test)
Select MandantenVerbindungID, convert(varbinary(128),MandantenBezeichnungKurz1) from dbo.TestSec

When i select the data with the first select statement i get the needed HEX-Values to decrypt. But when i make a

normal select (2. statement) i get 128 Byte Hex Code. But i need the result of the first statement.

OK: i can write the same statement in java an start. but then goes the password in plaintext across the network. Me idea was to get the encrypted data.

now i have the chance to encrypt but i have no data :)))))) sorry i must smile.

nils

|||

You cannot retrieve the cleartext data without decrypting it, so statement (1) will get the cleartext data, while statement (2) will get the ciphertext - that is to be expected. If you do not want to specify a password in statement (1), then you should create the SQL Server certificate such that it is encrypted by the database master key (that is the behavior if you don't specify a password during the creation of the certificate) - then you won't need to specify any password to use it for decryption.

Thanks
Laurentiu

|||

hi Laurentiu,

you are right. in this moment i see it. but i am unhappy. i have on both site (sql server and java) the keys and the certificate. i print out the key store, and have a look at it. i think i have the same certificate. but it doesn't work.

i test it and test it ... nothing.

i get the enrypted data from the ResultSet with getString (0x...) or by getBytes(). i convert the data into UTF-8 and so on. no result.

now i think i must change my way and create an ssl connection. but i have test it in the sql server configuration manager. change the protocol to required encryption, but i can not select an certificate.

then i start the mmc and import an certificate for the current user (admin). no result.

have you any idea to import the certificate. and after this must i connect from my programm an sslSocket and tunnel through this my jdbc connect? or on which way it works. (i have an other example found, where an ssl client and ssl server is realized and only bethween this is the ssl conncet. then the ssl server connect to the sql server with a normal jdbc connect.. is this right?

thanks for all ideas

nils

|||

Wait a second, the results you were getting before were correct, yo did succeed to decrypt. Maybe you're getting confused because the result of the decryption is binary - you need to add a convert call to cast the binary to nvarchar: 0x4E004200490054003100 is NBIT1. Try executing:

select convert(nvarchar(20), 0x4E004200490054003100)

Or do you mean that you cannot decrypt on the Java application side?

For the SSL questions, you should ask on the SQL Server Data Access Forum.

Thanks
Laurentiu

|||

Encryption/Decryption using SQL Server 2005 key management performing cryptography operations in .NET

Thought this might help. I wasn't able to find documentation on this anywhere.

But I came up with a way to do this.

--SETUP--

makecert -r -pe -n "CN=crossmatch.net" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -sky exchange -sy 12 -sv {PRIVATE KEY}.pvk {CERTIFICATE}.cer
cert2spc {CERTIFICATE}.cer {CERTIFICATE}.spc
pvk2pfx -pvk {PRIVATE KEY}.pvk -pi {PASSWORD - create at this point} -spc {CERTIFICATE}.spc -pfx {CERTIFICATE}.pfx

CREATE CERTIFICATE {CERTIFICATE_NAME}
FROM FILE = '{CERTIFICATE}.cer'
WITH PRIVATE KEY (FILE = '{PRIVATE KEY}.pvk',
DECRYPTION BY PASSWORD = '{PASSWORD}');

CLR

public class SqlCryptography
{
string m_FileName;
string m_PKeyPassword;
X509Certificate2 m_Certificate;
RSACryptoServiceProvider m_Encryptor;
RSACryptoServiceProvider m_Decryptor;

public string FileName
{
get{ return m_FileName; }
set{ m_FileName = value; }
}

public string PKeyPassword
{
get{ return m_PKeyPassword; }
set{ m_PKeyPassword = value; }
}

X509Certificate2 Certificate
{
get{

if( m_Certificate == null )
m_Certificate = new X509Certificate2( m_FileName, m_PKeyPassword );
return m_Certificate;
}
}

RSACryptoServiceProvider Encryptor
{
get{
if( m_Encryptor == null )
m_Encryptor = ( RSACryptoServiceProvider ) Certificate.PublicKey.Key;
return m_Encryptor;
}
}

RSACryptoServiceProvider Decryptor
{
get{
if( m_Decryptor == null )
m_Decryptor = ( RSACryptoServiceProvider ) Certificate.PrivateKey;
return m_Decryptor;
}
}

public SqlCryptography( ) : this( string.Empty, null )
{

}

public SqlCryptography( string certificateFileName, string password )
{
this.m_FileName = certificateFileName;
this.m_PKeyPassword = password;
}

public byte[] Encrypt( byte[] data )
{
return Encryptor.Encrypt( data, false );
}

public byte[] Decrypt( byte[] encryptedData )
{
return Decryptor.Decrypt( encryptedData, false );
}
}

IMPLEMENT

MAXIMUM BYTE[] LENGTH IS 117

crypt.SqlCryptography crypto = new crypt.SqlCryptography( @."C:\Users\Matt\CrossMatch.Security.pfx", "crossmatch" );
Encoding.Default.GetString( crypto.Decrypt( crypto.Encrypt( Encoding.Default.GetBytes( "TEST" ) ) ) );
Encoding.Default.GetString( crypto.Decrypt( crypto.Encrypt( Encoding.Default.GetBytes( "TEST TEST" ) ) ) );

Note: Importing the pfx into a certificate store you will lose the private key on the cert.

import OpenSSL certificate with private key into sql server 2005 express edtion

hey,

i have a problem, to import a self signed openssl certificate into the sql server 2005.

my final idea is to get encrypted columns from the database over an jdbc connection in a java client.

when i use a certificate generated by the sql server 2005, i can encryt columns of a table. then i catch the

the result in my java client. but in java, i need a keystore with the private key of the certificate.

ok. i have export the the certificate and the private key of the sql server 2005.

problem: in a keystore i can only import the certificate (signed public key) but not the private key.

my new idea is to import an openssl certificate or an certificate generated by the keytool (java) into the

sql server 2005 and encypt the data with the imported certificate. Problem: The SQL Server give me an

Exception: (Sorry i drag & drop the exception, is written in german)

Msg 15208, Level 16, State 1, Line 2

Die Datei für das Zertifikat, den asymmetrischen Schlüssel oder den privaten Schlüssel ist nicht vorhanden oder weist ein ungültiges Format auf.

My Import Statement is:

CREATE CERTIFICATE InsuranceCertOpenSSL

FROM

FILE = 'E:\master\keys\insuranceservice_tomcat_apr_x509_certificate_with_cygwin_openssl_20060630\insuranceservice_tomcat_x509_certificate_with_openssl.crt'

WITH PRIVATE KEY (

FILE = 'E:\master\keys\insuranceservice_tomcat_apr_x509_certificate_with_cygwin_openssl_20060630\insuranceservice_tomcat_x509_certificate_with_openssl_private.key',

DECRYPTION BY PASSWORD = 'testit2_',

ENCRYPTION BY PASSWORD = 'testit2_'

)

I use password encryption, and not the internal master key (or service master key)

Hope for help :)

nils

Hi Nils,

Could you try creating a certificate in keytool with a private key that is not encrypted? Then when you create it in SQL Server, you can omit the "DECRYPTION BY PASSWORD..." line.

It may simply be that the decryption of the certificate is failing.

Sung

|||

HI Sung,

thank you for answering.

i create a test certificate with private key in openssl and with keytool too.

i bring the certificate an the private key outside the sql server 2005 in the DER Format, because i read

that the certificate in SQL Server use this format.

OK.

i can import the certificate from openssl. (without the WITH PRIVATE KEY option).

CREATE CERTIFICATE InsuranceCertOpenSSL
FROM
FILE = 'E:\CertSqlServer2005InsuranceDatabaseOpenSSL.crt'

it work. but when i encrypt and decrypt somthing the sql server have no private key.

SELECT Cert_ID('InsuranceCertOenSSL')

DECLARE @.NAMEENC NVARCHAR(100)
DECLARE @.PWD NVARCHAR(100)
SELECT @.PWD = 'testit2_'
SELECT @.NAMEENC = EncryptByCert(Cert_ID('InsuranceCertOpenSSL'),N'O12345678901234MMM5678XXX901234567890 O12345678901234567')
SELECT @.NAMEENC as encode
SELECT CAST(DecryptByCert(Cert_ID('InsuranceCertOpenSSL'),@.NAMEENC, @.PWD) AS NVARCHAR(MAX)) as decode
GO

Result is null.

OK:

When i import the certificate with an private key.

CREATE CERTIFICATE InsuranceCertOpenSSL
FROM
FILE = 'E:\insuranceservice_tomcat_x509_certificate_with_openssl_DER.crt'
WITH PRIVATE KEY (
FILE = 'E:\insuranceservice_tomcat_x509_certificate_with_openssl_private_DER.key',
DECRYPTION BY PASSWORD = 'testit2_',
ENCRYPTION BY PASSWORD = 'testit2_'
)

it doesn' t work.

my problem is that the sql server 2005 enrcypt the private key by export. also the sql server 2005 import only a encrypted private key.

when the key is encrypted i can not convert into PEM. (with openssl)

My destination is to have encrpted data in my java application. and there i will decrypt.

two ways...

1.) import an expernal generated certificate and private key. this must be imported into the sql server. PROBLEM: can not import the private key (must by encrypted, i think so)

or

2.) create an certificate in sql server 2005 with private key. export both. (DER-Format). To convert the certificate into PEM is no problem. but to convert the private key into pem, openssl give me an exception:

$ openssl rsa -inform DER -in CertSqlServer2005InsuranceDatabase_private.key -o
ut CertSqlServer2005InsuranceDatabase_private_PEM.key -outform PEM
unable to load Private Key
6944:error:0D07207B:asn1 encoding routines:ASN1_get_object:header too long:asn1_
lib.c:150:

I will map this to import the certificate and the key into an keystore and opened in java.

3.) a new idea: encrpt by a symmetric key. but these keys can not be exported from the database. (but recreate in the database)

in the moment i try to create a equivalent symmetric key with openssl .

i don't know the best way. but i need one way. please help

thank you

nils

|||

I looked through the keytool documentation and it does not seem to support the private key file format that SQL Server expects.

How exactly did you create the private key files that you were trying to import in SQL Server?

You may want to use another tool to generate your certificate. Have you tried using makecert to generate the certificates?

Thanks
Laurentiu

|||

Hi Nils,

[Edit - see Laurentiu's comment above]

We do recommend using symmetric keys for encryption as the performance gain is significant, but you are right that they currently cannot be exported.

Do you have to do decryption on both the application and the database? It kind of sounds like you want the database to be able to encrypt and then your application will handle the decryption? If this is the case, you can possibly get around this by storing the full certificate (public/private key pair) with the application and storing only the public key of the ceriticate on the database. You should not need the private key to encrypt data, you only need the private key to decrypt.

Hope this helps,

Sung

|||

Hi Sung,

thanks for your help.

yes you are right. i create an application with web services an database connects. in the application will have the chance to distribute my "moduls". i know i can you ssl. in some parts i use it. but to connect to an database i dont't know to connect to the database over ssl. ok, i can programm a Listener-Thread on the database site, but for many connection i need a connection pool. this is not what i want.

you are right, i can create an external certificate/private key and use it to encrpt and decrypt outside the database. and the database encrypt only data. (i must think about, for a solution that i realy need the decryption in the database. i can encrypt only data-column, and not the searching-keys, and so on.)

soloution 1 (your):

create external certificate with openssl (for example) and encrypt on in the database with the imported certificate (without the private key).

solution 2:

create a symmetirc key in the application and the same in the database:

2.a.) in the database

CREATE SYMMETRIC KEY InsuranceSymDESKey
WITH
ALGORITHM = DES,
KEY_SOURCE = 'testit2_',
IDENTITY_VALUE = 'id_insurance_service_center'
ENCRYPTION BY CERTIFICATE InsuranceCert
go

2.b.) with openssl

openssl des -out insurance_sql_server_symmetric_key_openssl.key - ....

sorry i don't know the right syntax, to generated the same key. is this possible?

nils

|||

HI Cristofor,

>> I looked through the keytool documentation and it does not seem to support the private key file format that SQL Server expects.

The exported SQL Server Certificates in DER-Format. With

openssl x509 -in CertSqlServer2005InsuranceDatabase.crt -inform DER -out CertSqlServer2005InsuranceDatabase_PEM.crt -outform PEM
you can transform the certificate. Is that realy a differnet format for the primary key or is the exported key from the sql server only additional encrypted? In the sql server documentation is only reported that the key is encrypted, but no information about the algorithm or so.

>> How exactly did you create the private key files that you were trying to import in SQL Server?

1.) generate key

$ openssl genrsa -out insuranceservice_tomcat_x509_certificate_with_openssl_private.key -des3 2048

2.) create request

$ openssl req -new -in insuranceservice_tomcat_x509_certificate_with_openssl_private.key -out insuranceservice_tomcat_x509_certificate_with_openssl.csr

3.) create certificate

$ openssl x509 -in insuranceservice_tomcat_x509_certificate_with_openssl.csr -out insuranceservice_tomcat_x509_certificate_with_openssl.crt -req -signkey insuranceservice_tomcat_x509_certificate_with_openssl_private.key -days 365

4.) map private key from PEM into DER

$ openssl rsa -inform PEM -in insuranceservice_tomcat_x509_certificate_with_openssl_private.key -outform DER -out insuranceservice_tomcat_x509_certificate_with_openssl_private_DER.key -des3

5.) map crt from PEM into DER

$ openssl x509 -in insuranceservice_tomcat_x509_certificate_with_openssl.crt -inform PEM -out insuranceservice_tomcat_x509_certificate_with_openssl_DER.crt -outform DER

IMPORT into the sql server:

1.) (works, but without the private key)

CREATE CERTIFICATE InsuranceCertOpenSSL
FROM
FILE = 'E:\insuranceservice_tomcat_x509_certificate_with_openssl_DER.crt'

2.) doesn't work

CREATE CERTIFICATE InsuranceCertOpenSSL
FROM
FILE = 'E:\insuranceservice_tomcat_x509_certificate_with_openssl_DER.crt'
WITH PRIVATE KEY (
FILE = 'E:\insuranceservice_tomcat_x509_certificate_with_openssl_private_DER.key',
DECRYPTION BY PASSWORD = 'testit2_',
ENCRYPTION BY PASSWORD = 'testit2_'
)

When i remove the decryption, it doesn't work also.

>> You may want to use another tool to generate your certificate. Have you tried using makecert to generate the certificates?

i don't know the tool. but often the tools using in the background the openssl. You think this tool make another format the the sql server can understand (only the private key is needed).

i don't know the best way. but i will only send encrypted data over the network.

On which site i create the certificate (openssl or sql server) an then import and use each other is not imported.

also is not imported which tool i use to create the certificate/private key. i use all what give me an solution.

The problems are:

1.) import an certificate and the private key into the sql server 2005, generates by another software like the sql server 2005

2.) use the exported certificate and the private key from a sql server 2005.

In an microsoft document (http://www.microsoft.com/germany/technet/itsolutions/msit/security/sqldatsec.mspx) (german) i found in the last paragraph an information, that only certificates and private key can exchange between two or more sql server 2005. is this right?

Nils

|||

Hey Nils,

In regards to the connection problem, I will have to research that further.

I was trying to figure out a good solution for your second approach and I ran across two problems:

1) SQL Server and OpenSSL are probably generating the keys differently, therefore the keys created in 2a and 2b probably won't work with each other

2) I was talking about this with Raul and he mentioned that beyond that, the encrypted data is stored with extra headers and other info so it's not easy to extract the raw cipher text.

I'm not sure how to get around these two issues. The other alternative is to use JCE to handle encryption and decryption completely externally?

Sung

|||

It seems to me like you are trying to protect the data in transit, not only at rest. I strongly recommend against using the secret storage features in SQL Server 2005 for data in transit protection, instead, I would recommend using SSL between you server and client. You can find more information on this topic in MSDN (Encrypting Connections to SQL Server http://msdn2.microsoft.com/en-us/library/ms189067.aspx).

If my assumption is incorrect and/or you still need to be able to encrypt data in both your client application and SQL Server your best option would be to use certificates generated by makecert as Laurentiu mentioned.

Unfortunately the OpenSSL format for private keys and PVK are not compatible, but you can generate the certificate (public CER file and private key in PVK) using makecert, then to use it on OpenSSL you can use pvk2pfx tool to generate a PFX, as far as I remember, OpenSSL should be able to use DER encoded PFX files.

For the second solution you mentioned, creating the same symmetric key outside SQL Server. While in theory it is possible (KEY_SOURCE uses CAPI CryptDeriveKey to generate a 3DES key), you will face with the SQL Server specific headers that are not properly part of the ciphertext as well as the encrypted header once you are able to decrypt the data. I would strongly recommend against this approach.

I also include a link to a different discussion in the forum with a similar scenario (using an asymmetric key in a client), but in this case the client was using .NET code; while not exactly your scenario, it may be useful.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384472&SiteID=1

I hope this information was useful,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Nils,

makecert is a Microsoft certificate creation utility. It can generate certificates in the format that SQL Server 2005 understands. You can convert those certificates to other formats, such as PFX, using other tools (see http://www.kinook.com/blog/?p=10). I think openssl should know how to read PFX files, but I amy not familiar with openssl or keytool. My suggestion is to generate a certificate using makecert in the format understood by SQL Server, then convert it to PFX and use it with your application.

Thanks
Laurentiu

PS: My name is Laurentiu, Cristofor is my last name.

|||

Thanks to all for help. (and sorry for the mistake with your name laurentiu.)

a.) i use the pvktool to change the header of the certificates. transform the private key.

pvk -in CertSqlServer2005InsuranceDatabase_PVK_private.key -out CertSqlServer2005InsuranceDatabase_PEM_private.key

b.) transform the certificate

openssl x509 -in CertSqlServer2005InsuranceDatabase_DER.crt -inform DER -out CertSqlServer2005InsuranceDatabase_PEM.crt -outform PEM

c.) produce a keystore (pkcs12 format)

openssl pkcs12 -export -in CertSqlServer2005InsuranceDatabase_PEM.crt -inkey CertSqlServer2005InsuranceDatabase_PEM_private.key -out insuranceservice_sql_server_keystore_PKCS12.jks -name insurance_sql_server_cert

d) from java it is easer to work with jks-format keystore

set ksFileIn=insuranceservice_sql_server_keystore_PKCS12.jks
set ksFileOut=insuranceservice_sql_server_keystore_JKS.jks
set keyAlias=insurance_sql_server_cert
set ksPass=testit2_

java testclient.CopyKeyAndCertFrom_PKCS12_To_JKS_KeyStore %ksFileIn% %ksFileOut% %keyAlias% %ksPass%

e.) for this i write a small java programm (testclient.CopyKeyAndCertFrom_PKCS12_To_JKS_KeyStore)

package testclient;

import java.io.*;
import java.util.*;
import java.security.UnrecoverableKeyException;
import java.security.NoSuchAlgorithmException;
import java.security.KeyStoreException;
import java.security.KeyStore;
import java.security.KeyPair;
import java.security.PrivateKey;
import java.security.PublicKey;
import java.security.Key;
import java.security.cert.Certificate;
import java.security.spec.*;

public class CopyKeyAndCertFrom_PKCS12_To_JKS_KeyStore {
public static void main(String[] args) {
try {
System.out.println("Usage: CreateSQLServerKeyStore <pkcs12_keystore_in> <jks_keystore_out>"
+ "<key alias> <keystore password>");

System.out.print("\nstart");


if (args.length != 4) {
System.exit(-1);
}

String ksFileIn = args[0];
String ksFileOutJava = args[1];
String keyAlias = args[2];
char[] ksPass = args[3].toCharArray();

KeyStore ksJKS = KeyStore.getInstance("JKS");
ksJKS.load(null, ksPass);

KeyStore ksPKCS12 = KeyStore.getInstance("PKCS12");
java.io.FileInputStream fis = new FileInputStream(ksFileIn);
ksPKCS12.load(fis, ksPass);

java.security.Key key = null;
java.security.PublicKey publicKey = null;
java.security.cert.Certificate cert = null;
java.security.cert.Certificate[] certChain = new java.security.cert.Certificate[1];

key = ksPKCS12.getKey(keyAlias, ksPass);
if (key instanceof java.security.PrivateKey) {
cert = ksPKCS12.getCertificate(keyAlias);
certChain = ksPKCS12.getCertificateChain(keyAlias);
publicKey = cert.getPublicKey();
}

ksJKS.setKeyEntry(keyAlias, key, ksPass, certChain);

FileOutputStream fos = new FileOutputStream(ksFileOutJava);
ksJKS.store(fos, ksPass);
fos.close();

System.out.print("\nend");
} catch (Exception e) {
System.out.print(e);
}
}
}

e.) for testing (it works also) (makecert)

makecert -r -cy end -sky exchange -n "CN=Nils" -b 01/01/2006 -e 01/01/2011 -a sha1 -sv CertSqlServer2005InsuranceDatabase_makecert_x509_private_key.pvk CertSqlServer2005InsuranceDatabase_makecert_x509.cer

f.) after this you can import the certificate and the private key into the sql server 2005. in java you can also open the

private key and the certificate from the keystore.

...

FileInputStream fis = null;
KeyStore ks = null;
Key key = null;
PrivateKey privateKey = null;
PublicKey publicKey = null;
Certificate cert = null;
Cipher cipher = null;
String encryptedstring = null;
String decryptedstring = null;
byte[] encryptedtext = null;
byte[] decryptedtext = null;

fis = new FileInputStream(dbKeyStore);
ks = KeyStore.getInstance(dbKeyStoreTyp);
ks.load(fis, dbKeyStorePass);
key = ks.getKey(dbKeyStoreAlias, dbKeyStorePass);
if (key instanceof PrivateKey) {
privateKey = (PrivateKey) key;
cert = ks.getCertificate(dbKeyStoreAlias);
publicKey = cert.getPublicKey();
}

Security.addProvider(new BouncyCastleProvider());
cipher = Cipher.getInstance("RSA/1/NoPadding", "BC");
...

Now i can encrypt an decrpyt. BUT i have another problem.

In the sql server 2005 the DecryptByCert and the EnrcyptByCert Method give as the result the datatype varbinary(128).

ok. But the SQL Server doesn't give me the data. for example i write some sql statements.

drop table dbo.TestSec
go

CREATE TABLE dbo.TestSec
( MandantenVerbindungID nvarchar(5) NOT NULL,
MandantenBezeichnungKurz1 varbinary(128) NOT NULL
);
GO

INSERT into dbo.TestSec (MandantenVerbindungID,MandantenBezeichnungKurz1)
values ('NBIT1', EncryptByCert(Cert_ID('InsuranceCert'), N'NBIT1'))
GO

--(1. First SELECT STATEMENT)

SELECT MandantenVerbindungID,
DecryptByCert(Cert_ID('InsuranceCert'), MandantenBezeichnungKurz1, N'testit2_')
FROM dbo.TestSec

Result:

-- 1.Column: NBIT1
-- 2.Column: 0x4E004200490054003100

--(2. Second SELECT STATEMENT)

Select MandantenVerbindungID, MandantenBezeichnungKurz1 from dbo.TestSec

-- 1.Column: NBIT1
-- 2.Column: 0x6FF1AD39946A3C92E16E035446F66930852CA086BECEE05FE1AD95DD578A8281169DF696E3528580B35C9CCEB7AB
5DBBEE4C7F9FB34F75460A2A0DECAA146FD8F8CF9A258D49C2B1A8012918609F9C4CDF53A1439CEEFF21CCE2076DE
166BB123AD2788D2B8BE3BD1F4E967FFEFC84D839C485596CC8556641387D891224914

--(3. Third SELECT STATEMENT) (ONLY for Test)
Select MandantenVerbindungID, convert(varbinary(128),MandantenBezeichnungKurz1) from dbo.TestSec

When i select the data with the first select statement i get the needed HEX-Values to decrypt. But when i make a

normal select (2. statement) i get 128 Byte Hex Code. But i need the result of the first statement.

OK: i can write the same statement in java an start. but then goes the password in plaintext across the network. Me idea was to get the encrypted data.

now i have the chance to encrypt but i have no data :)))))) sorry i must smile.

nils

|||

You cannot retrieve the cleartext data without decrypting it, so statement (1) will get the cleartext data, while statement (2) will get the ciphertext - that is to be expected. If you do not want to specify a password in statement (1), then you should create the SQL Server certificate such that it is encrypted by the database master key (that is the behavior if you don't specify a password during the creation of the certificate) - then you won't need to specify any password to use it for decryption.

Thanks
Laurentiu

|||

hi Laurentiu,

you are right. in this moment i see it. but i am unhappy. i have on both site (sql server and java) the keys and the certificate. i print out the key store, and have a look at it. i think i have the same certificate. but it doesn't work.

i test it and test it ... nothing.

i get the enrypted data from the ResultSet with getString (0x...) or by getBytes(). i convert the data into UTF-8 and so on. no result.

now i think i must change my way and create an ssl connection. but i have test it in the sql server configuration manager. change the protocol to required encryption, but i can not select an certificate.

then i start the mmc and import an certificate for the current user (admin). no result.

have you any idea to import the certificate. and after this must i connect from my programm an sslSocket and tunnel through this my jdbc connect? or on which way it works. (i have an other example found, where an ssl client and ssl server is realized and only bethween this is the ssl conncet. then the ssl server connect to the sql server with a normal jdbc connect.. is this right?

thanks for all ideas

nils

|||

Wait a second, the results you were getting before were correct, yo did succeed to decrypt. Maybe you're getting confused because the result of the decryption is binary - you need to add a convert call to cast the binary to nvarchar: 0x4E004200490054003100 is NBIT1. Try executing:

select convert(nvarchar(20), 0x4E004200490054003100)

Or do you mean that you cannot decrypt on the Java application side?

For the SSL questions, you should ask on the SQL Server Data Access Forum.

Thanks
Laurentiu

|||

Encryption/Decryption using SQL Server 2005 key management performing cryptography operations in .NET

Thought this might help. I wasn't able to find documentation on this anywhere.

But I came up with a way to do this.

--SETUP--

makecert -r -pe -n "CN=crossmatch.net" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -sky exchange -sy 12 -sv {PRIVATE KEY}.pvk {CERTIFICATE}.cer
cert2spc {CERTIFICATE}.cer {CERTIFICATE}.spc
pvk2pfx -pvk {PRIVATE KEY}.pvk -pi {PASSWORD - create at this point} -spc {CERTIFICATE}.spc -pfx {CERTIFICATE}.pfx

CREATE CERTIFICATE {CERTIFICATE_NAME}
FROM FILE = '{CERTIFICATE}.cer'
WITH PRIVATE KEY (FILE = '{PRIVATE KEY}.pvk',
DECRYPTION BY PASSWORD = '{PASSWORD}');

CLR

public class SqlCryptography
{
string m_FileName;
string m_PKeyPassword;
X509Certificate2 m_Certificate;
RSACryptoServiceProvider m_Encryptor;
RSACryptoServiceProvider m_Decryptor;

public string FileName
{
get{ return m_FileName; }
set{ m_FileName = value; }
}

public string PKeyPassword
{
get{ return m_PKeyPassword; }
set{ m_PKeyPassword = value; }
}

X509Certificate2 Certificate
{
get{

if( m_Certificate == null )
m_Certificate = new X509Certificate2( m_FileName, m_PKeyPassword );
return m_Certificate;
}
}

RSACryptoServiceProvider Encryptor
{
get{
if( m_Encryptor == null )
m_Encryptor = ( RSACryptoServiceProvider ) Certificate.PublicKey.Key;
return m_Encryptor;
}
}

RSACryptoServiceProvider Decryptor
{
get{
if( m_Decryptor == null )
m_Decryptor = ( RSACryptoServiceProvider ) Certificate.PrivateKey;
return m_Decryptor;
}
}

public SqlCryptography( ) : this( string.Empty, null )
{

}

public SqlCryptography( string certificateFileName, string password )
{
this.m_FileName = certificateFileName;
this.m_PKeyPassword = password;
}

public byte[] Encrypt( byte[] data )
{
return Encryptor.Encrypt( data, false );
}

public byte[] Decrypt( byte[] encryptedData )
{
return Decryptor.Decrypt( encryptedData, false );
}
}

IMPLEMENT

MAXIMUM BYTE[] LENGTH IS 117

crypt.SqlCryptography crypto = new crypt.SqlCryptography( @."C:\Users\Matt\CrossMatch.Security.pfx", "crossmatch" );
Encoding.Default.GetString( crypto.Decrypt( crypto.Encrypt( Encoding.Default.GetBytes( "TEST" ) ) ) );
Encoding.Default.GetString( crypto.Decrypt( crypto.Encrypt( Encoding.Default.GetBytes( "TEST TEST" ) ) ) );

Note: Importing the pfx into a certificate store you will lose the private key on the cert.

Import only yhe changed data

I want to import via DTS to big table only the records that changed last day. can i do it without a time field? because this is a key table for DWH and not a fact table.
THX
InonIt would be bit difficult to import without a time field, which is key to compile the data.

If your task is about changed data then why not consider replication.|||Sure, but you need a staging environment...

1. bcp (I can't abvide DTS unless the data is in Excel or Access, even then...) in to a stage table
2. Write 3 sql statements to compare new data with old...determine, based on keys, which data was added, which data was deleted, and which data was updated..

3. Then INSERT, DELETE and UPDATE those sets of data...

Got a link somewhere...

Hold on...|||Here's the code:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28305|||You can use the BINARY_CHECKSUM function to determine whether data was changed.sql

Import in table identity column

I am importing data in the table which has primary key as identity.
Data file doesn't has the data for the column.
I created view which has the column except the identity column trying
to import but getting error
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ID', table 'MyTable; column
does not allow nulls. INSERT fails.
Note: Bulk Insert through a view may result in base table default
values being ignored for NULL columns in the data file.
The statement has been terminated.
Please help.
Thanks
--
FarhanUnless I am missing something, you should be able to import into the table
normally. Don't try to put anything in the Identity column. It will assign
the values automatically. Forget about creating a view to import into.
HTH
Paul
"fsoomro@.chartlinks.com" wrote:

> I am importing data in the table which has primary key as identity.
> Data file doesn't has the data for the column.
> I created view which has the column except the identity column trying
> to import but getting error
> Server: Msg 515, Level 16, State 2, Line 1
> Cannot insert the value NULL into column 'ID', table 'MyTable; column
> does not allow nulls. INSERT fails.
> Note: Bulk Insert through a view may result in base table default
> values being ignored for NULL columns in the data file.
> The statement has been terminated.
> Please help.
> Thanks
> --
> Farhan
>|||This works fine.
use northwind
go
create table t (colA int not null identity(1, 1) unique, colB varchar(25))
insert into t (colB) values('a')
insert into t (colB) values('b')
insert into t (colB) values('c')
execute master..xp_cmdshell N'bcp "select colB from northwind.dbo.t"
queryout d:\temp\test.tbl -Smyserver -T -c'
select * from t
delete t
go
create view myview
as
select colB from t
go
bulk insert northwind.dbo.myview
from 'd:\temp\test.tbl'
with
(
fieldterminator = '\t',
rowterminator = '\n'
)
go
select * from t
go
drop view myview
go
drop table t
go
execute master..xp_cmdshell N'del d:\temp\test.tbl'
go
AMB
"fsoomro@.chartlinks.com" wrote:

> I am importing data in the table which has primary key as identity.
> Data file doesn't has the data for the column.
> I created view which has the column except the identity column trying
> to import but getting error
> Server: Msg 515, Level 16, State 2, Line 1
> Cannot insert the value NULL into column 'ID', table 'MyTable; column
> does not allow nulls. INSERT fails.
> Note: Bulk Insert through a view may result in base table default
> values being ignored for NULL columns in the data file.
> The statement has been terminated.
> Please help.
> Thanks
> --
> Farhan
>sql

import ascii file with ssis and script

Hi, i've question about how to import an ascii-file in a sql 2005 table.
I want to import this file also with an unique key. There i first have to get the last key form the table and then raise this key. Next step is to use this key during the import.

How do i have to do this in ssis?
Thanks in advance

OlafNo problem.

While in your control flow, add a variable named MaxKey of integer type. Then in your control flow, right before the data flow task, add an Execute SQL task. Set its ResultSet to Single row. Setup the connection in it to point to your database and the for the SQLStatement, use: "select max(keyfield) from your_table". Click on the Result Set option on the left-hand side of the editor. Set the result name to 0 and chose User::MaxKey as the Variable Name. Click OK.

In your data flow right before the OLE destination, add a Script Component. Chose to use it as a transformation. Edit it. On the left, select "Inputs and Outputs". Expand Output 0 and then "Add Column" to the Output Columns folder and call it "NewKey. Make sure it is an integer data type big enough to hold a key big enough for your table. Then click on "Script" on the left to bring up the script parameters. Add "MaxKey" to the ReadOnlyVariables box. Then, click on the Design Script... button. Here's your script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent
Private NextKey As Int32 = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaximumKey As Int32 = Me.Variables.MaxKey ' Grab value of MaxKey which was passed in

' NextKey will always be zero when we start the package.
' This will set up the counter accordingly
If (NextKey = 0) Then
' Use MaximumKey +1 here because we already have data in the table, or we'll start with 0+1=1 if we don't
' and we need to start with the next available key
NextKey = MaximumKey + 1
Else
' Use NextKey +1 here because we are now relying on
' our counter within this script task.
NextKey = NextKey + 1
End If

Row.NewKey = NextKey ' Assign NextKey to our AdFormKey field on our data row
'
End Sub

End Class|||Phil,
Thansks for your answer, and i'm doing wel till step 'Script Component'. After i've clicked on 'script' and add 'MaxKey', i get an error message after i've clicked on button 'Design Script'. The message is: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container...' What do i wrong?
Thanks.

Olaf|||See the following post which explains the solution: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1

In short, save a copy of the SSIS variable (e.g. Me.Variables.Whatever ) in the OnPreExecute subroutine. Use the copy in lieu of Me.Variables.Whatever in you ProcessInputRow() subroutine.|||thanks jaegd, problem is solved. My i ask you an other question?
I get an error message 'Cannot create connector. the destination component does not hav any available inputs for use in creating a path' after i've connect the script component at the destination ole. What goes wrong? When i look to the properties of the OLE, i see some unuased input colummns.
Thanks in advance|||

jaegd wrote:

See the following post which explains the solution: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1

In short, save a copy of the SSIS variable (e.g. Me.Variables.Whatever ) in the OnPreExecute subroutine. Use the copy in lieu of Me.Variables.Whatever in you ProcessInputRow() subroutine.

I don't think this is the problem. I detailed the steps exactly as I use it. Either the OP has a typo, or he didn't define the variable in the right scope. The variable must be scoped to the package. The solution above is too much work.|||

Olaf vd Sanden wrote:

Phil,
Thansks for your answer, and i'm doing wel till step 'Script Component'. After i've clicked on 'script' and add 'MaxKey', i get an error message after i've clicked on button 'Design Script'. The message is: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container...' What do i wrong?
Thanks.

Olaf

You must define the variable when in the control flow. Make sure that the variable's scope is set to the package.

Then make sure you type the variable name in the ReadOnlyVariables section on the script parameters page, as I have indicated in my instructions. Do not include any extra spaces when typing this variable name in the ReadOnlyVariables box.|||

Olaf vd Sanden wrote:

thanks jaegd, problem is solved. My i ask you an other question?
I get an error message 'Cannot create connector. the destination component does not hav any available inputs for use in creating a path' after i've connect the script component at the destination ole. What goes wrong? When i look to the properties of the OLE, i see some unuased input colummns.
Thanks in advance

Please make sure you are using a destination OLE connector and not a source connector.|||Phil, First i had a data conversion before my destionation ole. After i've removed this is was possible to connect the script component. Sorry for my questions, it's new for me. But, isn't possible to use a data conversion and a script component?
Thanks, Olaf|||

Olaf vd Sanden wrote:

Phil, First i had a data conversion before my destionation ole. After i've removed this is was possible to connect the script component. Sorry for my questions, it's new for me. But, isn't possible to use a data conversion and a script component?
Thanks, Olaf

Sure it's possible. You must be sure that when you dropped in the script component that you selected "Transformation," not "Source" or "Destination."|||Phil, the script component propertie is 'Transformation', but still i can't connect a data covnversion and a script component add an Ole DB Destination
The situation that i want in the data flow is:
a 'Flate File source editor' > 'Data Conversion Tranformation editor' > 'OLE DB destination Editor' and also the 'Script Component' > 'OLE DB destination Editor'

Sorry again, but i'm new with this.
Thanks in advance.

Olaf|||

Olaf vd Sanden wrote:

Phil, the script component propertie is 'Transformation', but still i can't connect a data covnversion and a script component add an Ole DB Destination
The situation that i want in the data flow is:
a 'Flate File source editor' > 'Data Conversion Tranformation editor' > 'OLE DB destination Editor' and also the 'Script Component' > 'OLE DB destination Editor'

Sorry again, but i'm new with this.
Thanks in advance.

Olaf

Your OLE DB destination has to be at the END of the dataflow. You cannot have two in the same dataflow, unless you've split the dataflow with a multicast, conditional statement, etc...

Why do you have the OLE DB destination between the Data Conversion transformation and the Script component?|||Phil, Thanks for your reply. I think i've solved the problem. I've used an 'Union All' where i connected the 'Flate File source editor' and 'Script Component'. After the 'Union' comes a 'Data conversion' and then the 'OLE DB Destination'. I like to hear from you if this the right way.

Now i get an error message in the 'Execution Results': [DTS.Pipeline] Error: component "Union All" (14793) failed the pre-execute phase and returned error code 0x80070057.

What do I do wrong now?
Thanks.

Olaf|||Your flat file should hook into the data conversion transformation and then to the script component and then to the OLE DB destination.

FF -> Data Conversion -> Script Component -> OLE DB Destination.

No union is necessary.|||Phil, Thanks again for your fast reply. And it works.
Again thanks.
Olaf

Import / export question

When importing / exporting tables between databases in SQL 2005, the fields
lose any properties that have been set, i.e. primary key.
Any idea if there is a solution to this.
ThanksHello Chubby,
This behavior is by design. Importing/exporting data only copy/mapping data
from source to destination. It does not copy primary key/trigger etc.
If you want to achieve this, you need to use "Transfer SQL server objects"
in SSIS project. You could check "CopyPrimarykeys" table options in task
properties.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Many thanks
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:ne%231kACOGHA.768@.TK2MSFTNGXA01.phx.gbl...
> Hello Chubby,
> This behavior is by design. Importing/exporting data only copy/mapping
> data
> from source to destination. It does not copy primary key/trigger etc.
> If you want to achieve this, you need to use "Transfer SQL server objects"
> in SSIS project. You could check "CopyPrimarykeys" table options in task
> properties.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Welcome!
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.