Enable remote connections to sql express with a script

Question

I am deploying an application with sql server express 2008. In the prerequisites section of my application I have included:

As a result when a user installs my application it will install sql express as well.

Then I will be able to connect to that database engine as:

        try
        {
            // database should be in the same network
            SqlConnection conn =
                new SqlConnection(@"Data Source=.\sqlexpress; Integrated Security=True");
            conn.Open();
            MessageBox.Show("Connection succesfull");
        }
        catch
        {
            MessageBox.Show("Unable to connect");
        }

---

Now when I install a different application(client version) I will like to be able to connect to that database engine. I managed to connect to it by doing something like:

        try
        {

            SqlConnection conn =
                new SqlConnection(@"Data Source=192.168.0.120\sqlexpress,22559; USER=sa; PASSWORD=*********");
            conn.Open();
            MessageBox.Show("Connection succesfull");
        }
        catch
        {
            MessageBox.Show("Unable to connect");
        }

In order for that code to work I had to do the following:

---

So my question is:

How could I configure this with code? When I deploy my application I want my application to install sql express like it does but I also whant to enable tcp/IP connections, enable some ports and lastly create a password for the account "SA" because I am not able to connect to the database remotly if the sa account does not have a password.

Or maybe I am asking to much and I am doing the wrong thing. perhaps I should do all this just for the database that I am planing on deploying not the database engine. whatever is easier. I have had a hard time deploying this maybe it will be eassier to deoploy a local database along with a wcf service in order to create CRUD operations on the local database remotely.

EIDT

I found this 3 links that claim on doing something similar and I still cannot make it work.

1) http://support.microsoft.com/kb/839980

2) http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/c7d3c3af-2b1e-4273-afe9-0669dcb7bd02/

3) http://www.sql-questions.com/microsoft/SQL-Server/34211977/can-not-connect-to-sql-2008-express-on-same-lan.aspx

Answer 1

downloaded sql server express 2008 (SQLEXPR32_x86_ENU.exe) and place it in the root of my c drive. then I install it with the following parameters:

C:\SQLEXPR32_x86_ENU.exe /q /hideconsole /action=Install /features=SQL /instancename=SQLEXPRESS /enableranu=1 /sqlsvcaccount="NT Authority\Network Service" /AddCurrentUserAsSqlAdmin /skiprules=RebootRequiredCheck /TCPENABLED=1

I add /TCPENABLED=1 in order to enable TCP/IP

Answer 2

I suggest you to create modified bootstrapper package to install Sql Server 2005 Express with customzation.

As an alternative, you can also use a custom action in your installer to change the targeted server using SMO.

Something like this:

Server server = new Server( "ServerName\\InstanceName" ); 
server.ConnectionContext.Connect();
server.Settings.LoginMode = ServerLoginMode.Mixed;
server.Settings.Alter();

We use SMO object to create user login and associate user to our created application database.. even run sql script to create database if database is not available..

Refer these links:
Configuring SQL Express During Installation
Configuring SQL Server when included as a requirement

Note: Create your sql server connection string settings in App.config file rather than putting hardcore in code.. this will help you customize application first run customization e.g. database creation.

Answer 3

These might be of some help, I've had it on my todo list for a while for the computers I have to setup for my app to run with Sql Server 2008 Express. It's basically a way to setup a script that the SQL08exp installer will read and automate a lot of the setup according to what you set in the script.

http://digitalformula.net/articles/how-to-perform-an-unattended-installation-of-sql-server-2008-express/

http://blogesh.wordpress.com/2008/09/23/silent-install-of-sql-server-2008/