Problem With Encryption Using Self-signed SSL Cert

Apr 21, 2008

Hello,

I have a SQL 2005 test system set up that I need to test with protocol encryption, and had in mind to peform this test using a self-signed SSL certificate. I have read the guides and requirements from MS as well as the few blogs I could find on the subject and I've *almost* gotten it to work, but I'm failing at what seems to be the last step - getting clients to connect.. Here is what I've done:

-Created a self-signed certificate (I've tried using SSL Diagnostics from the IIS Diagnostics package, makecert.exe from .NET SDK, and OpenSSL) with the 'Server Authentication' purpose, with a CN that matches the server name, along with a private key. I've imported the cert in the local computer's personal store (also in LC's Trusted Root).
-SQL Server Configuration Manager sees the certificate and allows selecting it in the configuration. I've done this and selected 'Force Encryption' in the protocol settings.
-Restarted SQL Server, errorlog says 'The certificate was sucessfully loaded for encryption'.
-Tried connecting locally using SSMS and setting encryption: No problem.
-Exported the cert from the store and imported it into the LC's Trusted Root store on the client comp.

Here's where the problems begin.
1: Client is still apparently able to connect *unencrypted* to the SQL Server (I tested with SSMS on the client machine, it states under connection properties that the conn is not encrypted)

2: When trying to connect encrypted, SSMS returns an error during the pre-login handshake saying "The certificate's CN name does not match the passed value." When using the same cert in IIS there are no problems whatsoever, and the CN in the cert does indeed match the server name so I'm thinking the real error is hiding beneath the one that is thrown.

Also tried to configure the SQL Client Network Utility to force encryption from the client side, but this didn't seem to have any effect. I tried to use Network Monitor to capture network traffic, but since it appears to be encrypted (SQL encrypts login handshake anyway afaik) I couldn't get much out of it.. Does anyone have any tips?

View 2 Replies


ADVERTISEMENT

Cert Exams

Jun 21, 2008

how good are certmagic.com to prepare
for ccna and mcse exams . i have a long
learning ahead to do and will realy like
some practice questions to practice
myself . anyone have any idea about certmagic
exams , plz let me know

View 3 Replies View Related

SQL Server 7.0 Cert Book Question

May 28, 1999

I am looking for a set of books that realy cover the SQL
7.0 cert exams. Do you have any comments on the
following and or any suggestions.

Microsoft Sql Server 7.0 System Administration Training Kit
Microsoft Sql Server 7.0 Database Implementation Training Kit

Thank You,

Andy Robbins
Sr. Dba at Aurora Loan Services

View 3 Replies View Related

The Cert Under The Sql 2005 Data Directory

Aug 17, 2006

When sql 2005 is created a cert is also created in the Data directory of that instance. What is that cert? Is it the Service Master Key? I noticed that I can movecopydelete it while sql server is running so it doesn't appear to be very secure..

TIA,

Barkingdog

View 1 Replies View Related

Self-Signed Certificate

Mar 10, 2006

Can anybody can tell me how to do self-signed certificate on sql server? What is it ? Do we really need?
Many thanks.

View 12 Replies View Related

How To Add A Signed CLR Assembly

Aug 30, 2012

I'm trying to add an assembly to my SSDT project, and in my assembly, it requires EXTERNAL access, which also means that the assembly has to be signed, and an asymmetric key must be created from the assembly.  I can't figure out how to get this to work from the SSDT project.

View 8 Replies View Related

How To Determine Objects Signed By A Certificate

Apr 30, 2007

Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates what so ever. Am I missing something or is that part of the security to prevent gaining details about certificate usage?

View 5 Replies View Related

Load Multiple Signed Assemblies

Aug 18, 2006

I am trying to load multiple strongly named assemblies into the same database which are signed with the same .snk file (signed in Visual Studio).  I use the following code to create an asymmetric key and login as Books Online recommends:

CREATE ASYMMETRIC KEY SQLCLRKey FROM FILE = 'D:dbainAssembly.dll'

CREATE LOGIN CLRAssembler FROM ASYMMETRIC KEY SQLCLRKey

 GRANT UNSAFE ASSEMBLY TO CLRAssembler

GRANT EXTERNAL ACCESS ASSEMBLY TO CLRAssembler

REVOKE CONNECT SQL FROM CLRAssembler

Do I need to create a new login and asymmetric key for each assembly I load?  If so, do I need to sign each with a different key because its giving me an error message when I try to create 2 separate asymmetric keys/logins from 2 different assemblies which have been signed with the same .snk file.

The only way I've gotten everything to load properly is to create a separate key for each assembly and sign each, then create separate logins and asymmetric keys in the database.

Is this the only way to do this?  Or am I missing something?

View 5 Replies View Related

Problem Importing A Signed Number

Apr 9, 2008

I have a flat file that contains 20.00- value and I am trying to import it into a decimal field and it does not like the sign. What do I need to do to convert this via SSIS? Thank you

View 1 Replies View Related

Convert Character Representation Of A Signed Decimal

Apr 25, 2008

SQL/SERVER 2000:

Data transform task which copies data from a text file to a db table.

Text file field value = 0000000242E (signed decimal)

DB column data type = decimal(11,2)

How do I get this value correctly converted? Getting "invalid data value" error message.

thanks for any help

View 1 Replies View Related

SQL Security :: Encryption 2005 - User Defined Function For Encryption And Decryption

Oct 7, 2015

I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.

best way to encrypt and decrypt using user defined functions.Below is the query which is taking long time.

SELECT ID FROM table WITH (NOLOCK)
                     WHERE dbo.DecodeFunction(column) = 'value'

When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.

View 3 Replies View Related

(urgent)how Should I Treat Signed Decimal Values In Sql Server

Jul 30, 2007

Hi,
    I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.
   But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver  that particular column in sql server shows it as 0.00762711864406778
 So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778
 
any help will be appreciated.
Regards
Karen

View 2 Replies View Related

(urgent)how Should I Treat Signed Decimal Values In Sql Server

Jul 30, 2007



Hi,

I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.

But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778

So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778



any help will be appreciated.

Regards

Karen

View 7 Replies View Related

Differences Between Signed Vb Script Class And Vb Script Language

Jun 15, 2006

on ActiveX Script task

tia

View 3 Replies View Related

Encryption

Aug 4, 2000

I was wondering if anyone out there knows if it is possible to encrypt a particular field in a table, or encrypt a whole table. The info would remain on my database and not be sent out anywhere, but I just want an extra level of security against anyone who might try to break into the database.

View 2 Replies View Related

Encryption

Feb 23, 2000

Is there a function that can encryp the data in a table(or certain column)?
So if the table or column was query the person would see something like " !#)&%^#@ ". suggestion are welcome.


Kevin

View 1 Replies View Related

ENCRYPTION

Jun 19, 2002

Hello,

Is there a way to encrypt the data ( I mean actual data stored
in a table)in a SQLServer.
I know how to encrypt procedures, views, Net-libraries ......

Please help!!!!

Thanks.

View 1 Replies View Related

Encryption

Aug 23, 2002

How to get the encryption of certain characters, such as '12345' or 'hello'. Is any function to take regular characters and return the encryption form of those characters?
Please help.

View 1 Replies View Related

H5 Encryption?

Nov 6, 2005

My client requested that the password field of a login table be encrypted using H5 Encryption. I've been searching throughout the net and MSDN for the function or procedure and can't find it. Does anybody know how?

View 1 Replies View Related

Encryption

Apr 23, 2008

I developed a small VB6 application VB6 that get data from SAP sql database

Now they want that the users logon to this small app using the same username & password that they used in SAP

I can read the users' table, I can get the username
But the password is encrypted

Probably with a Function

Anybody know how can I encrypt the typed password so I can compare with the saved password

I don't want to view or modify them, just compare with the password typed by the user

Thanks

JG

View 1 Replies View Related

Encryption

Jul 7, 2005

Hello,
I am needing to migrate an Access database to Sql Server
Express. This database will be distributed as part of a
VB 6 application. This database will have some of the
columns encrypted. My understanding is that SQL Server
Express supports encryption. We will need to be able to
run queries on encrypted data like this, with
LastName being an encrypted column in the database.

Select LastName from Account where LastName = 'Smith'
Select LastName from Account where LastName Like 'Sm%'
Update Account Set where LastName = sLastName

Can this be done using SQL Server 2005 Express and VB 6?

Thanks!

View 1 Replies View Related

Encryption

Apr 4, 2007

hi all,
ive download some application, but they encrypt their Stored Proc and some Views..
1.how to encrypt SP?
2.is it possible to decrypt whateva that encrypted?

~~~Focus on problem, not solution~~~

View 19 Replies View Related

Encryption

Oct 3, 2007

Hai
I am new to SQL server and working on Encryption.I just want to know whether it is better to encrpt a data in database or in the code itself.I want to encrypt a number and not a text.which one would u suggest RC4 algorithm or pwdencrypt..any one plz reply soon..

View 13 Replies View Related

Reg: Encryption

Oct 3, 2007

Hi. Im new to SQLserver. I need to encrypt a column of datatype decimal. Which is better RC4 Algorithm or PWDencrypt() and PWDcompare().

Sundaresan.R

View 5 Replies View Related

Encryption

Feb 28, 2008

how i can open encrypted stored procedure

View 3 Replies View Related

Encryption

Apr 12, 2007

Hi,
i was wondering if the whole table can be encrypted using MS SQL Server 2005. This at present can be done using third party softwares.
Performance won't be an issue.

Thanks and regards,
Chandrachurh

View 6 Replies View Related

AES Encryption

Jan 11, 2008

I am using server 2005 running on a windows server 2003 platform. I am trying to do field encryption using symmetric key implemented by the AES algorithm. I created an AES key and apparently it encrypts the data with no problem, however when I try to retrieve the data after decryption the field is always null. I tried using the same process while using the DES or RC2 algorithm and I could both encrypt and decrypt information, however it does not decrypt for any of the AES algorithm i.e. AES_128, AES_192 and AES_256. Attached is a simple query I used to test it.


create symmetric key CCardKeys
with algorithm = aes_192
encryption by password = 'P@s$w0rD'

open symmetric key CCardKeys
decryption by password = 'P@s$w0rD'

declare @Id varchar(50)
set @Id = encryptbykey(key_guid('CCardKeys'),'Test')

select convert(varchar(50),decryptbykey(@Id))

close symmetric key CCardKeys

Is there something I'm missing with AES.

Thanks for taking the time to help me.

View 3 Replies View Related

Encryption

Apr 19, 2007

Here is my goal please let me know if it is possible.

I have installed sql express on clients machines. I don't want them to be able to view the sp's or the functions. I would like to go as far as not allowing them to see the tables. I tried with encrption but this is still breakable by the user using the DAC.



Thanks

View 15 Replies View Related

Encryption

Mar 5, 2007

I have a user table with Fields:

UID, name, SSN, phone,address. I need to encrypt all the fields except UID. My company recently moved to sql server 2005 and I have to encrypt old data. We do perform query search based on username and SSN

I have very shallow knowledge of encryption and indexes. I did looked at one of the articles on encrypting data on existing application but could not understand the indexing part(:()

Do i have to define new fields with varbinary as datatype(for ssn, name,phone number) ? I am asking this because in the demo only ssn_index has varbinary and the ssn field is still nvarchar?

Any help will be appreciated.

Thanks,

K

View 11 Replies View Related

Encryption

Sep 4, 2006

I have tried to encrypt by certificate and by symmetric key. In all cases the decryption comes back as null. Any ideas why?

I have used the code from a learnin tree course and the encryption works OK. I have also added a grant to the certificate to the login

View 1 Replies View Related

Encryption With Certificate

Apr 19, 2007

 I am trying to create a encrypted row in my database Everything here worked except that when i run the final query to decrypt the data It just comes up with null for each row. Even if i do a query to show me the rows that are not null It's like it is saying yeah there is data here but I am only going to show you null instead of what I am supposed to decrypt.Here is what I tried from start to finish Create Certificate
TestCert
Encryption By Password = 'Password'
With Subject = 'SQLCert',
Expiry_Date = '12/01/2050';


declare @Test nvarchar(50)
set @Test='123456789'

insert into testenc (testencry)
Values
(encryptbyCert(Cert_ID('TestCert'),@Test ))


select convert (Nvarchar(50),
DecryptByCert(Cert_ID('TestCert'),
testencry,N'Password')) As Test
from testenc 

View 2 Replies View Related

Data Encryption

Jul 20, 2007

i have to encrypt my data in sql2005 database using assymetric key encryption which i have done properly.But i have a doubt that while writing stored procedure i have to provide key information in it , that will be visible to everyone then whats the use of taht encryption. Can anyone tell me how can i write stored procedure without providing key information in it.
 
Divya

View 7 Replies View Related

Database Encryption

Aug 2, 2007

Hi,
 
  i am trying to encrypt my  database using dbEncrypt software.The encryption provides role based access ot the database.In that case developer can not call any stored procedure of that database. These two are conflicting things. Can anyone suggest me a way so that i can access the stored procedure through my code by checking the roles from the front end. is it possible??
Please reply soon.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved