Need a script which does settings of SQL SERVER Express to allow remote connection

[mania_comp]

HI experts..I need ur help..
VB.NET category
I need script that can be used to do settings for allowing SQL SERVER 2005 Express to allow remote connections.
The settings that we do manually after installation i.e to enable TCP/IP in remote connection in Configration Manager , start Browser service, set authentication mode,add sqlserver.exe in Windows Firewall exceptions, create a instance of SQL server express logon .... these settings I want do be done via through a SCRIPT.
I need this scrpit to get excuted during installation of my VB.NET Setup
Can anyone provide me the SCRIPT and also a way how to execute that during installation automatically?
:)

[sknake]

Why don't you just install it properly to begin with?

setup.exe /qb ADDLOCAL=SQL_Engine,SQL_Data_Files,SQL_Replication,Client_Components,Connectivity,SQL_SSMSEE INSTANCENAME=@@InstanceName@@ SAVESYSDB=1 SQLBROWSERAUTOSTART=1 SQLAUTOSTART=1 AGTAUTOSTART=1 SECURITYMODE=SQL SAPWD=@@Password@@ DISABLENETWORKPROTOCOLS=0 ERRORREPORTING=1 SQMREPORTING=0 ADDUSERASADMIN=1 INSTALLSQLDIR="%ProgramFiles%\Microsoft SQL Server\"

[mania_comp]

Ya ..but the command you specified needs to be executed by someone at command prompt.

My actual requirement is as follows::::
I am creating a VB.NET applicatoin which sql server express as backend. So now in SETUP deployment in am specifying SQL express 2005 as Pre-Requisite..
So when in cleint machine we we run setup it first installs SQL SERVER EXPRESS 2005 and then my vb.net application.
Here I need that script which will run automatically to do that remote settings for sql server express..so after installation ..cleint have to do no settings.. he sholud be able to run my vb.net aplication.
Is it possible to get that Script?

[sknake]

....what I do is have a front-end application that lets the user defines the parameters. The application then writes that command out to a "install.bat" file and ShellExecute() 's the file so the user doesn't have to run the command. I suggest you do the same. This is delphi/pascal code:

procedure TMSDE2005Fm.ButtonInstallClick(Sender: TObject);
Var
  txt: TextFile;
  aFileName, s: String;
begin
  aFileName := GetTempDir + '\PROD_Name.bat';

  AssignFile(txt, aFileName);
  ReWrite(txt);

  s := GetSetupString;

  WriteLn(txt, s);
  CloseFile(txt);

  MessageDlg('Setup will now begin for SQL Server 2005 Express.', mtInformation, [mbOk], 0);

  ShellExecute(0,'open',PChar(aFileName),nil,nil,SW_SHOW);

  ButtonCancel.Caption := '&Finish';

end;



function TMSDE2005Fm.GetSetupString: String;
begin
  Result := '"' + Trim(wwDBEditSetup.Text) + '" /qb';
  Result := Result + ' ADDLOCAL=SQL_Engine,SQL_Data_Files,SQL_Replication,Client_Components,Connectivity,SQL_SSMSEE';
  Result := Result + ' INSTANCENAME=' + Trim(wwDBEditInstanceName.Text);
  Result := Result + ' SAVESYSDB=1';
  Result := Result + ' SQLBROWSERAUTOSTART=1';
  Result := Result + ' SQLAUTOSTART=1';
  Result := Result + ' AGTAUTOSTART=1';
  Result := Result + ' SECURITYMODE=SQL';
  Result := Result + ' SAPWD=' + Trim(wwDBEditPassword.Text);
  Result := Result + ' DISABLENETWORKPROTOCOLS=0';
  Result := Result + ' ERRORREPORTING=0';
  Result := Result + ' SQMREPORTING=0';
  Result := Result + ' ADDUSERASADMIN=1';
  Result := Result + ' INSTALLSQLDIR="' + Trim(IncludeTrailingPathDelimiter(wwDBEditTargetDir.Text)) + '"';
end;

[mania_comp]

Yes.. I got your point..
But what I am trying to do is ....
My SQL-SERVER 2005 EXPRESS will run automatically as I have specified it as PRE-REQUISITE while creating Set-Up. So I need that script should run after SQL express is installed....
I can manage to provide instance name n all as it is fixed in my requirement... The thing I need is that script runs after sql express is installed...
IS it possible ?

[sknake]

Yes it is but why do something wrong from the start and go back and fix it? Why not just do it properly to begin with. You can add a custom installer action and create a class in your assembly to execute which contains the script. You're adding a lot of un-needed complexity to the situation.

[mania_comp]

Yes it is complex but i have already done the pre-requistite part and installation of sql express and my vb.net application in client machine is achieved successfully. The only one more thing i need is the script which can do the remote settings in SQL server express.

As u r saying it's possible by creating a class in vb.net, it will be a great help- if you can provide me the code for that class
:)

[sknake]

Yes but you cannot guarantee the SQL Server will be set up properly once it is on the client machine. I have deployed thousands of SQL Server instances and constantly a machine has AV or firewalls that bugger up the install. Likewise I have never tried to send SQL Configuration parameters to an existing installation.

You are going to create a support nightmare for yourself because you are almost there and don't want to go back to the beginning and do it properly. I'm not going to help with that :( McAfee and a few other AVs are guaranteed to bust an SQL Server installation.

Good luck and I wish you the best!

[mania_comp]

Your advice is correct and my way is complex really and I think it will be better for me to do settings initially as you said.
So i am ready to do as your suggested.
You gave me delphi code... where do i write that code ..in vb.net or i should create a bat file externally?

If i dont specify instance name then will it take default as 'localhost'?
And the code u gave..will that code do all settings for remote connection like... start browser service, set romete connection to TCP/IP and Named Pipes .??
Will it add sqlserver.exe in firewall exception as we do manually?

[sknake]

The parameters in setup.exe that I posted in:
http://www.daniweb.com/forums/post960476-2.html
are what you want to use to install the SQL Server. It will accept named pipe/remote connections.

You could call Process.Start() with all of the arguments but I write out a batch file so the user can see the command window with the arguments. I also write out the batch files because if the installation fails they may want to manually change or configure the options. It gives you a last-chance user-override.

It will not add SQL Server to the Windows Firewall Exceptions that I know of. However I have never had an issue with Windows Firewall blocking an SQL Server by default. AVG firewalls will block it though.