Implicit queries: columns

Showing posts with label columns. Show all posts

Friday, March 30, 2012

import question

I have a excel comma seperated file with 2 columns.

Column A contains names and Column B contains zips.

The problem is Column A is not consistent in it's structure. Some columns only contain one name where others contain two names just by last name seperated by a space or a slash.

For example

BOWERING WILKENSON
GEERS/DOODS

I'm trying to clean the data before importing into a sql table. I want to seperate the records for column A where there are two names and move the second name to a new row. Is there a method to do this during the import? Or, within Excel prior to the import using replace? I want to find instances where there is a space or slash and move the name after the space or slash and move to a new row?

Thanks,
-D-I think in Excel you can do a text to column and put "/" or space as the delimitter|||Instead of leaning up just import the fist column in a temporary table and clean it by using substring and charindex.

Import partial Sql table from Excel spreadsheet

I have this situation that I need to read a spreadsheet with user names into a sql table where user name is just one of the columns. I tried using oledb connection to read the spreadsheet and sqlbulkcopy to import into sql table. There was no error, but the data wasn't imported into sql.

Does anyone have any suggestion what I did wrong or what is the right way of doing this?

Thanks a lot.

Mia

Try the thread below for all you need, post again if you still have question. Hope this helps.

http://forums.asp.net/thread/1442470.aspx

|||Thank you. I will take a look at the posts/articles and let you know.

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)

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

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.