Hello,
I installed a verisign test certificate (and their root cert) and got the above mentioned error. Googling that error resulted in this link- http://support.microsoft.com/kb/900495 and not much else. Their "cause" does not apply to me because my SQL Server runs under the local system account, not a service account.
So, can anyone suggest another fix besides the one listed in that link? BTW, I did compile and run the code on that page. The only thing it did was throw a bunch of errors......
I am running on Windows Server 2003 Enterprise SP1 with SQL Server 2005 SP2.
Thanks!
Error: 26014 means:
"Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed."
Please check this blog and make sure your certificate is valid:
http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx
It seems that blog is everyone's answer to all SSL issues when it comes to SQL Server....
There isn't much control over the certs when using the test certs from one of the 3rd party vendors. Here is a dump from certutil-
================ Certificate 0 ================
X509 Certificate:
Version: 3
Serial Number: 6364d64c5790dd5cfc1e070df813ae68
Signature Algorithm:
Algorithm ObjectId: 1.2.840.113549.1.1.5 sha1RSA
Algorithm Parameters:
05 00
Issuer:
CN=Thawte Test CA Root
OU=TEST TEST TEST
O=Thawte Certification
S=FOR TESTING PURPOSES ONLY
C=ZA
NotBefore: 4/9/2007 2:22 PM
NotAfter: 4/30/2007 2:22 PM
Subject:
CN=slughorn.dbprotector.crossroads.com
OU=Thawte SSL123 certificate
OU=Go to https://www.thawte.com/repository/index.html
OU=Domain Validated
O=slughorn.dbprotector.crossroads.com
Public Key Algorithm:
Algorithm ObjectId: 1.2.840.113549.1.1.1 RSA
Algorithm Parameters:
05 00
Public Key Length: 1024 bits
Public Key: UnusedBits = 0
0000 30 81 89 02 81 81 00 a2 3c db c8 1d 31 81 c8 60
0010 79 09 d9 f1 61 72 ac c5 c8 57 4b 23 87 ee 05 7a
0020 01 e0 c8 3f 58 c8 af c0 eb 3c c1 61 0b ee 31 5d
0030 92 2e c7 26 b3 a7 ce 73 55 13 15 c7 be 42 ed df
0040 4f 0f 16 71 2a 23 e4 b7 fb 7b bf 55 18 3c 2d 5c
0050 95 6c 65 a6 88 bc 35 05 f4 68 fb b7 24 ec 09 f8
0060 a9 8f 70 64 0e b0 3e 56 62 c9 ed ca d1 2f 5f cb
0070 92 8b bc ce b2 92 57 c0 ad fc e4 3b 29 f2 b0 18
0080 92 97 21 0e e4 a5 47 02 03 01 00 01
Certificate Extensions: 3
2.5.29.19: Flags = 1(Critical), Length = 2
Basic Constraints
Subject Type=End Entity
Path Length Constraint=None
2.5.29.37: Flags = 0, Length = 16
Enhanced Key Usage
Server Authentication (1.3.6.1.5.5.7.3.1)
Client Authentication (1.3.6.1.5.5.7.3.2)
1.3.6.1.5.5.7.1.1: Flags = 0, Length = 26
Authority Information Access
[1]Authority Info Access
Access Method=On-line Certificate Status Protocol (1.3.6.1.5.5.7.48.1)
Alternative Name:
URL=http://ocsp.thawte.com
Signature Algorithm:
Algorithm ObjectId: 1.2.840.113549.1.1.5 sha1RSA
Algorithm Parameters:
05 00
Signature: UnusedBits=0
0000 84 88 42 cc 33 2e da b1 8b 61 c8 46 e3 34 b2 07
0010 36 0f 9d 27 63 a6 b2 b0 84 d4 23 7b 06 83 27 dc
0020 d7 1c 02 38 4b 25 10 37 25 ea 4a 27 da a3 b5 43
0030 72 aa 09 f3 07 b3 42 ff fd a6 5f d6 a4 1a 61 42
0040 ab 7f ac bb 70 dc 90 1b 44 d0 eb 53 64 8b 76 19
0050 83 08 17 3d 48 74 a3 03 f7 5f 8f 84 a4 4c 2c 3f
0060 d0 2e 0d 3a 16 c4 c1 08 2d 14 54 83 2b 87 16 b1
0070 d6 71 c5 c5 dd e5 54 6a e4 7b d3 46 65 2b 30 24
Non-root Certificate
Key Id Hash(sha1): 38 21 b5 9d 61 d5 e1 b8 74 97 78 1f c5 6d b3 17 de da 12 7a
Cert Hash(md5): 57 bc a6 f3 8d 33 c9 8e bb 04 d3 c4 0a 79 4b 9d
Cert Hash(sha1): aa a2 74 cf 6c 00 46 7e 04 4c f4 b3 ea fa a3 74 0a 8f 5c 0a
CERT_SHA1_HASH_PROP_ID(3):
aa a2 74 cf 6c 00 46 7e 04 4c f4 b3 ea fa a3 74 0a 8f 5c 0a
No stored keyset property
================ Certificate 1 ================
X509 Certificate:
Version: 3
Serial Number: 675c901500000000001c
Signature Algorithm:
Algorithm ObjectId: 1.2.840.113549.1.1.5 sha1RSA
Algorithm Parameters:
05 00
Issuer:
CN=Hagrid
DC=dbprotector
DC=crossroads
DC=com
NotBefore: 4/9/2007 12:47 PM
NotAfter: 4/8/2008 12:47 PM
Subject:
EMPTY (DNS Name=slughorn.dbprotector.crossroads.com)
Public Key Algorithm:
Algorithm ObjectId: 1.2.840.113549.1.1.1 RSA
Algorithm Parameters:
05 00
Public Key Length: 1024 bits
Public Key: UnusedBits = 0
0000 30 81 89 02 81 81 00 da e8 ab 36 f4 05 3e 80 42
0010 4f a1 eb a8 58 09 e4 f9 98 6f bc 86 50 43 c8 a3
0020 ba d4 da 99 9e cf 20 1b 40 42 50 92 64 39 99 c2
0030 01 d8 b1 7c e5 9b a6 fc 73 bd 2c 99 0e 09 cf c6
0040 9c c4 e1 7c 27 0d af 06 d1 fb b6 d7 86 25 1e 5a
0050 9f fa 84 e7 51 aa 92 67 a8 bc 2b 62 a2 2b f0 bc
0060 3c 6a d2 a2 f6 0e 59 23 7e c3 68 33 3e d2 63 c3
0070 91 b1 0e 8a 11 4c 3d 1e 46 ca 16 33 0a 0d 67 5a
0080 65 c8 ae 25 18 17 c7 02 03 01 00 01
Certificate Extensions: 9
2.5.29.15: Flags = 0, Length = 4
Key Usage
Digital Signature, Key Encipherment (a0)
2.5.29.14: Flags = 0, Length = 16
Subject Key Identifier
19 40 a9 11 7d 5d fb 23 3c 67 17 bc fd 2e 63 e7 59 7b 2a b8
1.3.6.1.4.1.311.21.7: Flags = 0, Length = 31
Certificate Template Information
Template=Computer - Exportable Key(1.3.6.1.4.1.311.21.8.5138224.4110583.14633635.5428750.2310230.215.11292564.15266621)
Major Version Number=100
Minor Version Number=6
2.5.29.35: Flags = 0, Length = 18
Authority Key Identifier
KeyID=7c 44 09 cf c6 83 8e 11 54 9b d7 e3 50 f4 31 45 f4 fb 01 9c
2.5.29.31: Flags = 0, Length = 111
CRL Distribution Points
[1]CRL Distribution Point
Distribution Point Name:
Full Name:
URL=ldap:///CN=Hagrid,CN=hagrid,CN=CDP,CN=Public%20Key%20Services,CN=Services,CN=Configuration,DC=dbprotector,DC=crossroads,DC=com?certificateRevocationList?base?objectClass=cRLDistributionPoint
URL=http://hagrid.dbprotector.crossroads.com/CertEnroll/Hagrid.crl
1.3.6.1.5.5.7.1.1: Flags = 0, Length = 12c
Authority Information Access
[1]Authority Info Access
Access Method=Certification Authority Issuer (1.3.6.1.5.5.7.48.2)
Alternative Name:
URL=ldap:///CN=Hagrid,CN=AIA,CN=Public%20Key%20Services,CN=Services,CN=Configuration,DC=dbprotector,DC=crossroads,DC=com?cACertificate?base?objectClass=certificationAuthority
[2]Authority Info Access
Access Method=Certification Authority Issuer (1.3.6.1.5.5.7.48.2)
Alternative Name:
URL=http://hagrid.dbprotector.crossroads.com/CertEnroll/hagrid.dbprotector.crossroads.com_Hagrid.crt
2.5.29.37: Flags = 0, Length = 16
Enhanced Key Usage
Client Authentication (1.3.6.1.5.5.7.3.2)
Server Authentication (1.3.6.1.5.5.7.3.1)
1.3.6.1.4.1.311.21.10: Flags = 0, Length = 1a
Application Policies
[1]Application Certificate Policy:
Policy Identifier=Client Authentication
[2]Application Certificate Policy:
Policy Identifier=Server Authentication
2.5.29.17: Flags = 1(Critical), Length = 27
Subject Alternative Name
DNS Name=slughorn.dbprotector.crossroads.com
Signature Algorithm:
Algorithm ObjectId: 1.2.840.113549.1.1.5 sha1RSA
Algorithm Parameters:
05 00
Signature: UnusedBits=0
0000 bb 6f a7 86 30 21 96 ed e4 9c d8 53 f5 67 2e c9
0010 9f b7 26 fb 41 b4 0c 6d 81 49 79 8d 45 0f f1 f1
0020 5a 1d 15 a7 4a 42 8c f4 77 6f 14 4d d7 ff 8d fc
0030 19 90 e0 5e 7d c3 be 48 f7 db 2c 7f e8 19 bf 61
0040 71 3b 3f 76 ae 35 48 7f 73 aa 1f 5e a0 b0 bc 1a
0050 71 0f 33 77 6a b8 d3 8e c4 65 0b 86 39 09 40 ec
0060 cf 0e 56 11 a3 ee 92 06 da 27 9b f8 60 02 6c 7b
0070 c1 d3 ac 9a 84 7c 9d 1b 85 f7 95 42 85 bc 6d 3d
0080 ef 00 8e bc 42 92 ec 52 33 2e 01 91 51 0e 3b 35
0090 46 d9 a8 ce 00 bb 0e 0f bc 01 b6 4a d7 12 cf 64
00a0 a3 7a 2f 9c 67 2c 83 d4 6c ee ad b9 4d bd 33 cf
00b0 31 bf da ed fb 52 5b c1 fe 25 53 17 e8 8b df a9
00c0 ce 5e cb af c2 9b 6e 5a e1 b5 1b 46 d1 f1 40 5a
00d0 0f 08 e4 ef 59 5e 5b 5b c7 95 47 b2 2d ba d0 ea
00e0 d4 e0 cf 94 3b 29 04 5c 4d c8 0a 0b 57 52 eb 60
00f0 cd cb d5 f0 00 c6 f3 89 86 96 31 0b 64 ae 4c 0d
Non-root Certificate
Key Id Hash(sha1): 19 40 a9 11 7d 5d fb 23 3c 67 17 bc fd 2e 63 e7 59 7b 2a b8
Cert Hash(md5): e7 f8 20 8f 19 5a b1 43 46 1e d7 dc 5d 78 2d 48
Cert Hash(sha1): 68 ca 1e d3 81 eb 23 5d 2e 64 e1 02 5c 6e 28 e1 fd a7 2f 38
CERT_ISSUER_PUBLIC_KEY_MD5_HASH_PROP_ID(24):
f8 bb f0 33 2e 21 d9 56 fd 01 8d 73 75 b8 08 fa
CERT_SUBJECT_PUBLIC_KEY_MD5_HASH_PROP_ID(25):
cf 2b e1 e7 2c f4 2f de ca 57 83 f7 4d 51 cf f4
CERT_SHA1_HASH_PROP_ID(3):
68 ca 1e d3 81 eb 23 5d 2e 64 e1 02 5c 6e 28 e1 fd a7 2f 38
CERT_KEY_PROV_INFO_PROP_ID(2):
Key Container = 2716a703c2b974b82dac9d51ce0f9cdd_6fda5789-4c9a-4c8f-8c3e-79950eb4238b
Provider = Microsoft RSA SChannel Cryptographic Provider
ProviderType = c
Flags = 60
KeySpec = 1
CERT_FRIENDLY_NAME_PROP_ID(11):
Computer - Exportable Key
CERT_KEY_IDENTIFIER_PROP_ID(20):
19 40 a9 11 7d 5d fb 23 3c 67 17 bc fd 2e 63 e7 59 7b 2a b8
CERT_MD5_HASH_PROP_ID(4):
e7 f8 20 8f 19 5a b1 43 46 1e d7 dc 5d 78 2d 48
CERT_SIGNATURE_HASH_PROP_ID(15):
78 07 74 19 73 c2 a1 e2 d6 b3 47 82 b8 10 60 e8 6c 8f 5d aa
2716a703c2b974b82dac9d51ce0f9cdd_6fda5789-4c9a-4c8f-8c3e-79950eb4238b
Private Key:
PRIVATEKEYBLOB
Version: 2
aiKeyAlg: 0xa400
CALG_RSA_KEYX
Algorithm Class: 0xa000(5) ALG_CLASS_KEY_EXCHANGE
Algorithm Type: 0x400(2) ALG_TYPE_RSA
Algorithm Sub-id: 0x0(0) ALG_SID_RSA_ANY
0000 52 53 41 32 RSA2
0000 ...
024c
Encryption test passed
CertUtil: -store command completed successfully.
Certificate 0 is the test Thawte cert. Can you see anything wrong with it?
Certificate 1 is a Windows CA issued cert based on the Computer template. It is a version 2.0 cert. It does not show up in the SQL Server Management console, but it works if I put its hash into the registry. Now I need to get a 3rd party cert working.
I am installing the certs in the Local Computer certificate store. SQL Server is running under the local system account.
From the blog-
1) It is in the local computer cert store and SQL Server runs under that account
2) Time stamps are good
3) From the above dump - Enhanced Key Usage
Server Authentication (1.3.6.1.5.5.7.3.1)
Client Authentication (1.3.6.1.5.5.7.3.2)
4) I don't see this anywhere, AT_KEYEXCHANGE
5) Does not apply to me.
Thanks for the help!
|||The first one does not have:
Key Usage
Digital Signature, Key Encipherment (a0)
where Key Encipherment (a0) means AT_KEYEXCHANGE,
A certificate without AT_KEYEXCHANGE is not meant for Data Exchange and cannot be used as certificate by SQL Server. Please check the purpose of your certificate.
Another Anonymous asked similar question days ago, I'm not sure if that is you. Please register a real name if possible.
|||No, that wasn't me.
Anyway, it wasn't a problem with the cert. It was a problem with the horrible process required to get SQL Server to use it. Basically,
1) Create CSR with IIS
2) Request cert from Verisign
3) Complete pending cert request in IIS
4) Open cert snap-in for local computer, export the new cert using PFX format, ensuring to export private key.
5) Re-import cert from PFX format.
After that nonsense, SQL Server's tools recognized the cert the used it no problem.....
I'm not sure if the other 3rd party certs will work this way or not. I haven't tried them.
No comments:
Post a Comment