"Encrypt" argument not supported in CF 2.0 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!

"context connection" and MultipleActiveResultSets ...Can have both at the same time?

Hi,

When I enable MultipleActiveResultSets in the "context connection" (SqlConnection), I get an error:

System.InvalidOperationException: The only additional connection string keyword that may be used when requesting the context connection is the Type System Version keyword.

Can we have MARS in the "context connection"?

note: I'm doing this to support multiple open datareaders in a CLR stored procedure.

Thanks!

Andy

While I could not find explicit documentation on WHY, from my tests you CANNOT leverage MARS for inprocess SQL Server connections. Since you cannot append the MARS= in the connection string I simply tried to use the feature hoping context connections would allow it...

I ran this code:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void usp_MARS()

{

string strSQLGetOrder = "Select * from Sales.SalesOrderDetail WHERE SalesOrderID = 43659";

string strSQLUpdateInv = "UPDATE Production.ProductInventory SET Quantity=Quantity-@.amt WHERE (ProductID=@.pid)";

SqlConnection marsConnection = new SqlConnection("context connection=true");

marsConnection.Open();

SqlCommand readCommand = new SqlCommand(strSQLGetOrder, marsConnection);

SqlCommand writeCommand = new SqlCommand(strSQLUpdateInv, marsConnection);

writeCommand.Parameters.Add("@.amt", SqlDbType.Int);

writeCommand.Parameters.Add("@.pid", SqlDbType.Int);

using (SqlDataReader rdr = readCommand.ExecuteReader())

{

while (rdr.Read())

{

writeCommand.Parameters["@.amt"].Value = rdr["OrderQty"];

writeCommand.Parameters["@.pid"].Value = rdr["ProductID"];

writeCommand.ExecuteNonQuery();

}

}

marsConnection.Close();

}

};

And I receive the following runtime error on execute of the proc:

Msg 6522, Level 16, State 1, Procedure usp_MARS, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_MARS':

System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

System.InvalidOperationException:

at System.Data.SqlClient.SqlInternalConnectionSmi.ValidateConnectionForExecute(SqlCommand command)

at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)

at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at StoredProcedures.usp_MARS()

|||

MARS is not supported for server-side CLR code. However, you can use cursors to simulate similar kinds of behavior in some circumstances. See the ResultSet sample for a fairly painless way to do that. The latest samples MSI is located at http://msdn.microsoft.com/sql/downloads/samples/default.aspx. After you install the Samples MSI, by default you'll find the ResultSet sample at drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\ResultSet. There is a readme file in that folder which explains how to compile the CLR code, create the database objects, and run the sample.

|||

Hi Andy!

MARS is not available with context connections in SQL Server 2005.

Of cause, you can always use out-of-proc connection (where MARS can be enabled) to own server from CLR UDP.

|||Thanks!

"context connection" and MultipleActiveResultSets ...Can have both at the same tim

Hi,

When I enable MultipleActiveResultSets in the "context connection" (SqlConnection), I get an error:

System.InvalidOperationException: The only additional connection string keyword that may be used when requesting the context connection is the Type System Version keyword.

Can we have MARS in the "context connection"?

note: I'm doing this to support multiple open datareaders in a CLR stored procedure.

Thanks!

Andy

While I could not find explicit documentation on WHY, from my tests you CANNOT leverage MARS for inprocess SQL Server connections. Since you cannot append the MARS= in the connection string I simply tried to use the feature hoping context connections would allow it...

I ran this code:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void usp_MARS()

{

string strSQLGetOrder = "Select * from Sales.SalesOrderDetail WHERE SalesOrderID = 43659";

string strSQLUpdateInv = "UPDATE Production.ProductInventory SET Quantity=Quantity-@.amt WHERE (ProductID=@.pid)";

SqlConnection marsConnection = new SqlConnection("context connection=true");

marsConnection.Open();

SqlCommand readCommand = new SqlCommand(strSQLGetOrder, marsConnection);

SqlCommand writeCommand = new SqlCommand(strSQLUpdateInv, marsConnection);

writeCommand.Parameters.Add("@.amt", SqlDbType.Int);

writeCommand.Parameters.Add("@.pid", SqlDbType.Int);

using (SqlDataReader rdr = readCommand.ExecuteReader())

{

while (rdr.Read())

{

writeCommand.Parameters["@.amt"].Value = rdr["OrderQty"];

writeCommand.Parameters["@.pid"].Value = rdr["ProductID"];

writeCommand.ExecuteNonQuery();

}

}

marsConnection.Close();

}

};

And I receive the following runtime error on execute of the proc:

Msg 6522, Level 16, State 1, Procedure usp_MARS, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_MARS':

System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

System.InvalidOperationException:

at System.Data.SqlClient.SqlInternalConnectionSmi.ValidateConnectionForExecute(SqlCommand command)

at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)

at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at StoredProcedures.usp_MARS()

|||

MARS is not supported for server-side CLR code. However, you can use cursors to simulate similar kinds of behavior in some circumstances. See the ResultSet sample for a fairly painless way to do that. The latest samples MSI is located at http://msdn.microsoft.com/sql/downloads/samples/default.aspx. After you install the Samples MSI, by default you'll find the ResultSet sample at drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\ResultSet. There is a readme file in that folder which explains how to compile the CLR code, create the database objects, and run the sample.

|||

Hi Andy!

MARS is not available with context connections in SQL Server 2005.

Of cause, you can always use out-of-proc connection (where MARS can be enabled) to own server from CLR UDP.

|||Thanks!