Encrypt Connection with Certificate

I have been trying to create a certificate for use with SQL2005. I found openSSL to create a cert but I am not sure how to use it.

When I go into SQL Config Manager / Protocol Properties / Certificate Tab... I do not see any certificates. The list is empty. Where are these certs pulled from and how can I create one on my own?

Here are the Reqs:

Certificate Requirements

For SQL Server 2005 to load a SSL certificate, the certificate must meet the following conditions:

The certificate must be in either the local computer certificate store or the current user certificate store.

The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.

The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).

The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).

The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.http://msdn2.microsoft.com/en-us/library/ms187798.aspx|||Is this the cert that SQL is looking for? I thought it was a local computer cert generated through CA.|||Data Source=server.domain.com;Initial Catalog=master;User ID=SQLuser;Encrypt=True;TrustServerCertificate=Tru e

this works but I want to create my own cert.

Encrypt argument does not work in SqlConnection.ConnectionString?

Is "Encrypt" not support in Compact Framework 2.0 SqlConnection string?

Trying to connect to sql2005 db using SSL from WM5/.net 2.0 pda and am receiving the below error. I can connect fine w/o the "encrypt" argument and the same connection string with "encrypt" connects fine from XP workstation to sql db w/SSL.

"Unknown connection option in connection string: encrypt."

Connection String I used:

sqlConnection = newSqlConnection("Data Source=mysqlserver;Initial Catalog=sometable;UID=username;PWD=Password;Encrypt=true;");

Thanks,

Jim

Hello everybody

I have the same problem. I'm using mobile PC with WinCE 4.2 on board. And I need the connection to be encrypted. How can I make this? The CF1.0/2.0 doesn't allow this feature. :(

Also I can use the VPN connection. But my device doesn't have this function (the function is present in disable mode). May be there is another application that realize VPN for WinCE 4.2?

Thank you.

Best regards,

Sergey

|||

This works in my code. I'm showing you the function that calls GetConnectionString so you'll see where the string is being passed.

PublicSharedFunction GetConnection(ByVal strDatabaseName AsString) As SqlCeConnection

Dim cn As SqlCeConnection

cn = New SqlCeConnection(GetConnectionString(strDatabaseName))

Return cn

EndFunction

PublicSharedFunction GetConnectionString(ByVal strDatabaseName AsString) AsString

Return"Data Source=" & strDatabaseName & ";SSCE:Database Password=xxxxxxxx;SSCE:Encrypt Database=True"

EndFunction

|||

Why did you write the 'ssce' near connection string properties? Is it rule or something else?

Also, I need connect to the Sql Server on the host (not mobile sql server) and I use the SqlConnection class for it. Can I use such connection string? I didn't find the information in the msdn about its description.

Sorry, but I can't check your advise now and see the result, I'm away from my workstation. That is why I want to know more. And I'll check it latter.

|||

To Clearify - I'm trying to encrypt the sql connection to the host using, not to a local installation of SQL Mobile. I believe a couple of replies address encrypting data or the database of a local sql mobile db.

Jim

|||Sorry, I did not notice you were not using sqlce.|||This means that Encrypt is not supported in Compact Framework 2.0 SqlClient.SqlConnection|||

This code sample does not work on a WM 5 device with cf 2.0 but works ok if "Encrypt=true" is removed. Am I doing something wrong? Same code works fine on XP with .Net 2.0 when connecting to same ssl enabled sql server. How can I encrypt a sql connection from pda to sql server?

Thanks,

Jim

sqlConnection = new SqlConnection("Data Source=someSql2005Server;Initial Catalog=someSqlTable;"

+ "UID=myUsername;PWD=somePassword;Encrypt=true");

|||The compact framework is not the full .NET framework. Some features are disabled or just not implemented. If you get an error that the connection could not be established because of the encrypt feature not being available it's just not implemented!

Encrypt argument does not work in SqlConnection.ConnectionString?

Is "Encrypt" not support in Compact Framework 2.0 SqlConnection string?

Trying to connect to sql2005 db using SSL from WM5/.net 2.0 pda and am receiving the below error. I can connect fine w/o the "encrypt" argument and the same connection string with "encrypt" connects fine from XP workstation to sql db w/SSL.

"Unknown connection option in connection string: encrypt."

Connection String I used:

sqlConnection = new SqlConnection("Data Source=mysqlserver;Initial Catalog=sometable;UID=username;PWD=Password;Encrypt=true;");

Thanks,

Jim

Hello everybody

I have the same problem. I'm using mobile PC with WinCE 4.2 on board. And I need the connection to be encrypted. How can I make this? The CF1.0/2.0 doesn't allow this feature. :(

Also I can use the VPN connection. But my device doesn't have this function (the function is present in disable mode). May be there is another application that realize VPN for WinCE 4.2?

Thank you.

Best regards,

Sergey

|||

This works in my code. I'm showing you the function that calls GetConnectionString so you'll see where the string is being passed.

Public Shared Function GetConnection(ByVal strDatabaseName As String) As SqlCeConnection

Dim cn As SqlCeConnection

cn = New SqlCeConnection(GetConnectionString(strDatabaseName))

Return cn

End Function

Public Shared Function GetConnectionString(ByVal strDatabaseName As String) As String

Return "Data Source=" & strDatabaseName & ";SSCE:Database Password=xxxxxxxx;SSCE:Encrypt Database=True"

End Function

|||

Why did you write the 'ssce' near connection string properties? Is it rule or something else?

Also, I need connect to the Sql Server on the host (not mobile sql server) and I use the SqlConnection class for it. Can I use such connection string? I didn't find the information in the msdn about its description.

Sorry, but I can't check your advise now and see the result, I'm away from my workstation. That is why I want to know more. And I'll check it latter.

|||

To Clearify - I'm trying to encrypt the sql connection to the host using, not to a local installation of SQL Mobile. I believe a couple of replies address encrypting data or the database of a local sql mobile db.

Jim

|||Sorry, I did not notice you were not using sqlce.|||This means that Encrypt is not supported in Compact Framework 2.0 SqlClient.SqlConnection|||

This code sample does not work on a WM 5 device with cf 2.0 but works ok if "Encrypt=true" is removed. Am I doing something wrong? Same code works fine on XP with .Net 2.0 when connecting to same ssl enabled sql server. How can I encrypt a sql connection from pda to sql server?

Thanks,

Jim

sqlConnection = new SqlConnection("Data Source=someSql2005Server;Initial Catalog=someSqlTable;"

+ "UID=myUsername;PWD=somePassword;Encrypt=true");

|||The compact framework is not the full .NET framework. Some features are disabled or just not implemented. If you get an error that the connection could not be established because of the encrypt feature not being available it's just not implemented!

Enable AWE under SQL2005 EE -

Hello,

I understand that SQL2005x64 bits ignore AWE enable in SQL because it can address all the memory without having to use AWE. How about 32 bits SQL2005, does it support AWE enabled?

I emailed the guy who presented on the topic at PASS a few weeks ago and here's his response. It may be a bit overkill, but hopefully answers your question. :)

This is a bit complicated.

Yes, SQL 2005 supports AWE and the behavior is different from 2000 in it is dynacmically adjustable with no server restart.

On x64 and I64 regardless of the AWE setting we use the underlying windows AWE mechanism to allocate the memory. This is a performance optimization because using this AWE mechanism in the kernel requires one less lock. However there is no mapping/unmapping from lower to upper memory as you would in the 32-bit world – just flat addressing. Customers can get confused when they look at DBCC MEMORYSTATUS on 64-bit and see AWE pages allocated.

Also, on 64-bit make sure that the SQL service account needs to be granted “lock pages in memory”.

Also, take a look at Slava’s BLOG – it explains many of the memory behaviors of the engine in great depth.

?http://blogs.msdn.com/slavao

Thanks,
Sam Lester (MSFT)

Enabel AWE under SQL2005 EE -

Hello,

I understand that SQL2005x64 bits ignore AWE enable in SQL because it can address all the memory without having to use AWE. How about 32 bits SQL2005, does it support AWE enabled?

I emailed the guy who presented on the topic at PASS a few weeks ago and here's his response. It may be a bit overkill, but hopefully answers your question. :)

This is a bit complicated.

Yes, SQL 2005 supports AWE and the behavior is different from 2000 in it is dynacmically adjustable with no server restart.

On x64 and I64 regardless of the AWE setting we use the underlying windows AWE mechanism to allocate the memory. This is a performance optimization because using this AWE mechanism in the kernel requires one less lock. However there is no mapping/unmapping from lower to upper memory as you would in the 32-bit world – just flat addressing. Customers can get confused when they look at DBCC MEMORYSTATUS on 64-bit and see AWE pages allocated.

Also, on 64-bit make sure that the SQL service account needs to be granted “lock pages in memory”.

Also, take a look at Slava’s BLOG – it explains many of the memory behaviors of the engine in great depth.

?http://blogs.msdn.com/slavao

Thanks,
Sam Lester (MSFT)

empty resultset from sql2005

Hi,

I have used Red Gates SQL bundle to make an identical db on sql2005 express from sql 2000. When i run my query against the sql2000 i get what I want, but when i run it against the sql2005, i get nothing.

Anyone got any suggestions to why?

web.config:

<!-- <add name="TPLConnectionString" connectionString="Data Source=[SQL2000],1433;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=??;Password=????;"

providerName="System.Data.SqlClient" />

-->

<

addname="TPLConnectionString"connectionString="Data Source=[SQL2005]\\SQLEXPRESS,1433;Integrated Security=true;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=???;Password=???;"providerName="System.Data.SqlClient" />

default.aspx.cs

dbConnection.Open();

String ISPQuery ="SELECT STATEMENT";SqlCommand command =newSqlCommand(ISPQuery, dbConnection);SqlDataReader reader = command.ExecuteReader();

Please help!!!

Best regards

Terje Kristensen

Could you try using '(local)\SQLEXPRESS'rather than '[SQL2005]\\SQLEXPRESS''

I had a little difficulty with SQLEXPRESS being a bit precious. I don't know if it'll help but if you're out of ideas... Otherwise I'm being stupid and the '\\' is a typo :/

|||

Hi.

Thank you for replying. It did not help changing the servername to (local), but when I removed the following:

;Integrated Security=true;Network Library=DBMSSOCN

it suddenly worked!

Thank you for helping.

Best regards

Terje Kristensen