Connection String Encryption For SQL Server Mobile
Sep 28, 2006
I'm seeking suggestions on how to go about encrypting a database connection string in the compact framework. It's something that I thought would be relatively straight forward as I've accomplished it without much difficulty in the full framework, but I'm stuggling to find a solution.
I've been trying to implement a solution using public/private key asymmetric encryption using the RSA provider. I've essentially been trying to encrypt the connection string once on my development machine so that I can include both the encrypted connection string value and public key in either code or an external file. I'd then keep the private key out of harms reach. The problem I've encountered in doing this is that that RSA provider seemingly only allows new "in session" keys to be generated and I can't figure out a way of importing a public key from a file or other similar means.
I found the following code which is meant to allow a key to be retrieved from a signed assembly or an SNK file, which I've converted to VB but haven't been able to get working:
After the code seems to successfully prepare the CspParameters from an SNK files, I get an unspecified error when executing ImportParameters on the RSA provider.
I'd imagine this to be a common requirement, but haven't been able to find much information specific to the compact framework on the web. Any guidance would be much appreciated.
We have this web application (asp classic) that needs to connect to SQL 2005 server. Can somebody help me on how can I encrypt the password as well as the ID used in connecting to the server (SQL 2005 server)? Is it possible to encrypt the password and ID being used in the connection string?
Any reply from you guys would be appreciated. Many thanks! =)
I have code that creates a SQL Mobile database on a server. It has worked in many environments, but I have one customer experiencing an issue. It will not create the database on their server. I would appreciate any thoughts.
The exact error message I get is "the operating system does not support encryption"
I am getting it on the line: SQLCeEngine.CreateDatabase()
Here is the code: SQLCeEngine = New SqlServerCe.SqlCeEngine(SQLConnectionString) ReturnValue += ": Declare new" SQLCeEngine.CreateDatabase() ReturnValue += ":Create" SQLCeEngine.Dispose() ReturnValue += ": Dispose"
Here is the error message I get from our application- the part after Err: is the actual error message from the system: Data Source = D:EVADATAWebServiceDownloads est;Password=test;Persist Security Info=False;: Declare new:Error!:Err:The operating system does not support encryption.
When I go into SQL Express and create a SQL Mobile database using it, it can be created. So, I have to believe it can be done on the server, but I don't know why the code can not do it. (e.g. FileConnect Object ExplorerServer Type: SQL Server compact Edition and create a database). The database is successfully created if I do it manually this way. It even creates fine if I select the 'Encrypt' button.
The machine is a Windows 2000 Server sp4 version 5.00.2195 SQL 2000 is installed SQL 2005 Express is installed I ran the SQLServerCE31-EN.msi file and it is installed. (I have also re-run and repaired it) I have also copied over the newest version of our code from scratch.
I am doing some research into SQL Mobile to determine if it suitable for particular project and I am trying to identify the encryption available on the device. I have read that there is 128bit encryption available in data sheets for the product, but is this of AES standard or equivalent?
Amoungst the things I have also read is that window mobile 5 supports this with the .Net CF etc, but I am more concerned with actual .sdf file encrption at the moment.I have read the SQL Bol as well where they demonstrate how to secure the database but I dont see where the encryption type is specified.
Also, if this is not a feature of SQL Mobile, is there practical way to meet this requirement using SQL Mobile?
So, in summary, is the encrpytion used in SQL Mobile of AES standard or equivalent?
I have a central database server that is runnning on SQL 2005 standard edition and Windows server 2003 standard as OS.
I realise that I can use SQL statements to encrypt and decrypt the data inside the standard SQL.
However, how do I read and write the data via an web application coded in C#.net and is also running on the same machine?
Another issue is, I need to replicate some of the data in this SQL standard over to a SQL mobile running on a mobile device running on Windows CE 5.0.
The mobile device also needs to read and write data to the encrypted data via a C#.net application.
Question is, with all these requirements to be met, can I use AES? I know that AES is not available on Windows XP and Windows Server 2000 and I cant find AES in the .net compact framework.
how do i go about ensuring security? how do I ensure that the symmetric key is the same both on the SQL standard and SQL mobile?
We have a need for getting data from sqlserver DB on premise to the cloud. DB is not encrypted currently, other applications are accessing it but those applications are on premise. Following link talks about encrypted connection, but is it possible to encrypt only one of the port connection. we can add a custom port.URL...
I have a working Pocket PC 2003 app that uses RDA to exchange data with SQL Server 2000. I'm attempting to port it to a Windows CE 5.0 device. (I'll be porting to Mobile 5 in the near future, but the gadget I have at the moment is CE).
I installed SQL Server 2005 Mobile Edition on the computer running IIS according to package directions, and used the wizard to set up a virtual directory with sscesa30.dll.
The directory I was previously using with sscesa20.dll is still there, and still works.
I set up the virtual directory to use basic authentication without SSL. (I know that's not advisable). I have confirmed that I can reach the URL using the username and password I supplied to the wizard.
When I try to pull data I get the following:
------------------- SQL CE Exception Error Code: 80004005 Message : An error has occurred on the computer running IIS. Try restarting the IIS server. Minor Err.: 28022 Source : Microsoft SQL Server 2005 Mobile Edition
I have tried restarting IIS without effect. Any clues?
But when i'm deploying it to Pocket PC the error "An error message cannot be displayed because an optional resource assembly containing it cannot be found" appears.
2. When I'm doing te same on Pocket PC Emulator everything is normal.
Off course, I have the .sdf file on Pocket PC which is placed to Storage Card into root directory.
If anyone knows where I get a mistake, help me please!
I am writing an application using VB.Net 2005 for the Windows CE 5.0 device and it is connecting to a SQL Server 2005 Mobile Edition Database. The trouble I'm having is establishing a connection to a SQL Server Mobile database on the device.
Here is the code I am using:
Code Snippet Public gConnectionString As String = "Data Source=Program FilesMobAppMobDB.sdf;Persist Security Info=False;"
Code Snippet
Imports System.Data.SqlServerCe
Public Class DBManager
Public Shared gSQLCEConnection As SqlCeConnection
Public Shared Function OpenDB() As Boolean
If gSQLCEConnection IsNot Nothing Then
Throw New InvalidOperationException("Connection already open.") End If gSQLCEConnection = New SqlCeConnection(gConnectionString) Try
gSQLCEConnection.Open() 'Error occurs here Return True Catch ex As SqlCeException
MsgBox(ex.Message & vbCrLf & ex.HResult & vbCrLf & ex.NativeError & vbCrLf & ex.Source) Return False End Try End Function
In the immediate window I recieve the following messages:
A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll
A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll
A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll
A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll
A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll
A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll
A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll
A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll
A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll
I believe that the first few lines are caused by the icons/pictures that I have included with my app, but the SqlCeException line occurs when the connection to the .SDF file is trying to be established.
Here is the exception output:
Message: "" (Blank) HResult: -2147024882 NativeError: 0 Source: SQL Server 2005 Mobile Edition ADO.NET Data Provider
Programs under Remove Programs on the PDA are: Microsoft .NET CF 2.0 ENU-String R... Microsoft .NET Compact Framework... Microsoft SQL Client Microsoft SQL Mobile 2005 Microsoft SQL Mobile 2005 [EN] Microsoft SQL Server 2005 Compact... Microsoft SQL Server 2005 Compact... Microsoft SQL Server 2005 Compact... Microsoft SQLCE 2.0 Microsoft SQLCE 2.0 Dev
Any ideas on what could be stopping this connection from being established? It was working before but all of a sudden just stopped. I have warm booted, cold booted, and rebuilt with no luck and also checked that SQL Query Analyzer is not running on the PDA and that the connection string is valid. The call to OpenDB occurs in the form_load of the startup object.
I have a child package where the ConnectionString property of a Connection Manager is set by a Parent Package Variable Configuration. I set up a script task that brings up a message box with the value of the ConnectionString property right before the dataflow task.
MessageBox.Show(Dts.Connections["CPU_*"].ConnectionString.ToString());When I run the parent package, the message box shows that the connection string is changing with every iteration, but in the dataflow it always draws the data from the same source.
The connection manager is an ADO.Net type, RetainSameConnection is set to False, and I've been researching this for days.
(Update 2/23/2015): To make this stranger, when I look at the diagnostic logs, they tell me that when the new connections are being opened they are using the new connection strings.
Hi,could you tell me please what are the ways of the query stringencryption in T-SQL ? I would like to have a storage procedure thatencrypts e.g. http://123.23.43.1/pagegen.asp?param1=23¶m2=124 intohttp://pagegen.asp?code=fdgfehiqrzvhe and convert it back to theorginal url.Thanks in advanceOmi
I added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.
Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.
Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.
On Windows XP systems I get the following issue when trying to browse the MSDB folder in SSIS
Client unable to establish connection Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I have noticed another post where several others have noticed the same issue. It appears to only occur on Windows XP installations. Is there a workaround or fix for this?
I don't understand why I am having this error. It does create the database on AddSubscription() method but it is failing opening the database on Synchronise(). I have also tried uninstalling and then reinstalling all the SQL Mobile components in the following order.
I am developping a non-managed C++ application for PocketPC using a SQL Server mobile database.
The application is compiled for PocketPC 2003 and uses SQL Server Mobile v2. I use Visual Studio 2005. But I need to compile the application for Windows Mobile 5.0 devices. So I installed the WM5 SDK and had the WM5 into my project configuration.
The "ssceoledb.h" which I include incluses the "transact.h" file. But my problem is that this file is only provided with the PocketPC 2003 SDK and not in the WM5 SDK. So I cannot use the WM5 configuration project with SQL Server. I also tried with the last SQL Server Mobile 2005 (ie v3.0) and the "ssceoledb30.h" also includes "transact.h".
Did I miss something to install ? Do you know how I can resolve the problem ?
Using MS VS 2005 (incl SQL Server Mobile) MS Pocket PC 2005 SDK
I am working on a project that builds for Pocket PC on both Mobile 2003 and Mobile 5. The project uses/will use SQL Server Mobile to store local data.
Project created from new with support for both platforms. I include required header files ssceerr30.h and ssceoledb30.h.
Project builds fine in WM2003 configuration, release and debug.
When I build for WM5 the compiler cannot find the header file transact.h. This is included from within ssceoledb30.h. Same as under WM2003.
In WM2003 configuration if I highlight the ssceoledb30.h include in Visual Studio and open the header, it takes me to <Visual Studio dir>SmartDevicesSDKSQL ServerMobilev3.0. I then locate the include for transact.h and do the same, which takes me <Visual Studio dir>SDKPocketPC2003include. The file exists.
If I repeat the above 'browsing' under the WM5 configuration, ssceoledb30.h takes me to a different copy in the WM5 SDK directory. There is no diff between the file here and the other copy used by WM2003. If I attempt to open transact.h - file does not exist.
Fix (which I'm not too sure about, i.e. is it OK?) - If I copy transact.h to the WM5 SDK directory, the project builds.
Why has transact.h disappeared from WM5 SDK? I can find no ref's to this problem anywhere. Is my installation of the WM5 SDK corrupt? What else could I be missing? Is there a sample for SQL Server mobile (like the NorthwindOLEDB sample) that comes configured to build for WM5?
Hi folks, I'm new to Windows Mobile progamming, and new to this forum. Apologies in advance if I'm asking a boneheaded question, but I've done searches and can't find anything directly applicable to my problem.
I'm currently walking through the published MS tutorial in setting up an SQL Server 2005 Mobile application that subscribes to a publication on SQL Server 2005 to exchange information.
I've gotten almost all the way through... successfully set up the server components, creating the publication, etc. On the mobile side, I've been able run the cab files to install the SQL Mobile components and to create the project, add the reference to the dll, and instantiate an engine object. It compiles.
The step in the tutorial after that, though, where you specify the data source from the "data" menu, I've got a problem. When I try to use the "new connection" dialog from choosing the data connection, "MS SQL Server Mobile Edition" doesn't show up as a choice. I've tried choosing any of the other combinations, and in teh subsequent "Connection properties" section my database, SQLMobile (as in their sample) is available, but I get an error when I choose it and click OK. Clicking on "Test Connection" gives me a connection successful message.
My guess is that something about the Mobile server side components is not installed correctly on my development machine, but honestly I have no idea how to begin to fix it. Has anyone seen this problem before and know how to resolve it?
Your time and any knowledge sharing is greatly appreciated. Thank you, -Dana
I'm trying to connect to Desktop SQL Server 2000 from Windows mobile PC Emulator (VS 2005). I need a direct connection using connection string to SQL Server 2000 through local wireless network without IIS.
Bellow is the code that I use. After executing this code I get an error on line Conn.Open(). Error says SQL Server does not exist or access denied. SQL is un and running, and I can log in using SA username from the desktop. Even if I chance IP for another SQL server in my connection string I still get the same error. There is no firewall of any kind running.
Dim connectionSTR As String = "Persist Security Info=False;Integrated Security=False;Server=192.168.0.202,1433;initial catalog=MyDB;user id=sa;password=;"
Dim Conn As SqlConnection
Conn = New SqlConnection(connectionSTR)
Conn.Open()
If Conn.State = ConnectionState.Open Then MessageBox.Show("Open") End If
About my environment: SQL Server 2000 is running on Desktop PC with Windows XP SP2. Application which I need to connect to SQL Server is in Visual Studio 2005. I execute the application in Windows Mobile PC Emulator and try to connect to SQL Server from emulator.
When I try and connecto to SQL CE I always get an invalid operation exception. I"m afraid that I did not follow the proper install for Orcas Beta 2. I can't remember if I was supposed to uninstall SQL Mobile 2005 first or not.
All I know is when I try and use my SQL CE I can't connecto to a DB / sdf file ?
Any help would be appreciated I"m just starting to use SQL CE.
Hi I am trying to write the following connection string:public static SqlConnection connect() { SqlConnection sqlConn = new SqlConnection("server=9.0.3042;database=Menu;Connection Lifetime=300"); return sqlConn; } I am trying to connect to a sql database using c# code. The code above is in a seperate file. The code below is the code used to talk to the database SqlConnection sqlConn = connection.connect(); sqlConn.Open(); SqlCommand menubar = new SqlCommand("Select * from Menu_table", sqlConn); SqlDataAdapter dataAdapter5 = new SqlDataAdapter(); dataAdapter5.SelectCommand = menubar; DataSet dataSet5 = new DataSet(); dataAdapter5.Fill(dataSet5); DataTable selcartest4 = dataSet5.Tables["table"]; However the connection is just timing out like I have the wrong address set for the connection on the first bit of code posted any heklp would be great cheers J
Hey guys, sorry to bother you with something so newbish, but I am trying to connect to a remote sql server db, using vb6. I want to use ado to do so but have been having problem with some of the samples I have found online.
I will probably just test the connection out on an existing database like pubs or northwind.
Can anyone provide me a code snip please?
This forum has always been a great resource for me, thanks again guys!
Hello,I am doing the web based project for the final year of my cousein Information Technology using Asp.net and ms-sql server.But to startwith that I need to connect my web pages to the database.So Can anyone tell me how to connect my page web to the ms-sqlserver using Asp.net(Connection String)?.It will be better if someonegive me the code for the connection string along with the explainationso that I don't have problem understanding it.Any kind of help will be appreciated.Thank You.Suchen Chodankar.
This is a great tutorial and it's a shame one of the more important steps was missed. In the €œCreate the snapshot user€? section you you find the steps to create the snapshot_agent account. Then in the €œCreate the snapshot folder€? section you find the share and folder permissions. However, at no point do the instructions advise you about adding the snapshot_agent to the SQL Server Logins. The result is that agent cannot perform the initial snapshot but you won't find this out until 50 steps later after Step 10 in the section €œCreate a new subscription".
To get back on track, openthe Object Explorer's Security section and add the snapshot_agent to your logins. Then using the "User Mappings", set an appropriate level for the SQLMobile database role. Once completed you then need to run the agent.
Right-click the SQLMobile publication you created and select "View Snapshot Agent status". From that dialog you can select "Start" to run the agent. When it completes, you can return to the tutorial section "Create a new subscription" and continue with the tutorial.
I am studying the tutorial in SQL Server 2005 Mobile Edition Books Online, and the topic is Creating a Mobile Application with SQL Server Mobile. I have got a problem when creating a new subscription after created a new SQL Server Mobile database. And the problem is shown below:
New Subscription Wizard
- Beginning Synchronization (Success)
- Synchronizing Data (100%) (Error) Messages * Failure to connect to SQL Server with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect. HRESULT 0x80004005 (29061)
* 無法完æˆ?作æ¥ã€‚
- Finalizing Synchronization (Stopped)
- Saving Subscription Properties (Stopped)
Before I have met this problem, I have finished all the task. And I can browse the localhost web site by using anonymous account even I use internet explorer or browse the directly in IIS.
Hi,I trying to connect to the sql server in the network. The sqlserver is in the machine called "pinnacle". I am Using the following connection string to connect and i am getting the error "Sql server does not exist or Access denied"Public databaseConnectionStringSQL As String = "Data Source=(pinnacle);Initial Catalog=cms;Trusted_connection=True;User Id=dbuser;Password=password"Any help will be appriciated.Reagrds,Thiyasoft
i'm using sql server 2005 express edition as the database server for my web application. Right now, i want to deploy it and the problem is, my hosting is using sql server 2000. So, is it possible to deploy my database that has been created in sql server 2005 in the sql server 2000? I'm afraid that the connection string for sql server 2000 is different from sql server 2005. In addition, i'm using forms authentication in my web application. So, some data regarding to the forms authentication like dbo.asp_net.membership table, dbo.asp_net.roles table is in my database. Is it the table that has been created like dbo.asp_net.membership can be used in sql server 2000 since it is created by asp.net forms authentication function?
I have the Visual Studio 2005 Team Suite trial version and the SQL Server 2005 trial (which came with team suite) installed in my machine. When I installed the SQL Server 2005 I installed it in the Windows Authentication mode. So every time I open SQL Server 2005 it doesnt ask for Username/Password (Its greyed out). The only thing available were: Server Type: Database Engine ServerName: ServerSue Authentication: Windows Authentication. Here is my Problem: I created a small application in C#. In the web.config file I created the following: <appSettings> <add key="resumecon" value="SERVER=ServerSue;database=Resume;"/> </appSettings> Then in the Code behind I created the following string: string strResCon = ConfigurationManager.AppSettings["resumecon"].ToString(); Then in method (for a Login page) I created the connection string as follows and wrote code to insert some values into the Resume database. I called this method on a button click event. SqlConnection conLogin = new SqlConnection(strResCon); When I run the page and when I click the button I get the following error: Login failed for user ''. The user is not associated with a trusted SQL Server connection. I think some thing is wrong in the addkey of webconfig? How do I change this?
I am slightly confused about SQL Server connections strings. When I used the following connection string: string strConn = "server=deciusawa;database=xxxx;uid=xxxx;pwd=xxxx"; I get an error saying "Format of the initialization string does not conform to specification starting at index 13. " I spent ages trying to figure out why this was and I eventually discovered that adding an "@" symbol before the string fixed it, i.e. string strConn = @"server=deciusawa;database=xxxx;uid=xxxx;pwd=xxxx"; Most of the examples I have seen to not use the "@" symbol so I am just wondering what is does and when you need to use it? Thanks, Mark
I am using SQL server express 2005. I can drag and drop a grid control on to my web form and choose the data source to point to a SQL server database. Then datais nicely displayed on my web form. Now I need to manually create my connection to the database as I need to read from a text file and then populatea database table with those values I read from the text file. I have not had any success with this though and I am stuck and need some help. Below is my C# code and I list the web.config code where my connection string is stored. Note that I can trace into the C# line of code below. If I then do a "Quick Watch" on "myConnection" in the debugger, the followingerror message is displayed: ServerVersion 'myConnection.ServerVersion' threw an exception of type 'System.InvalidOperationException' string {System.InvalidOperationException} So I don't know what the problem is here. If someone can help me out I would be forever thankfull
C# code: SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["TheDividerConnectionString"]);
Here is the connection string as defined in the web.config file: <connectionStrings> <add name="TheDividerConnectionString" connectionString="Data Source=BVCOMPUTERSQLEXPRESS;Initial Catalog=TheDivider;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>
