Member Avatar for 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?
:)

  • 2 Contributors
  • 9 Replies
  • 493 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by sknake

Dani AI

Generated

Short summary and a practical path forward (two options). is correct: the cleanest, most reliable approach is to pass the right parameters to SQL Server Express during unattended install so the instance is configured up-front. If that is not possible (you already install SQL Express as a prerequisite), a post‑install custom action that runs with elevated rights can make the remaining changes automatically.

A minimal, safe post‑install workflow

  • Enable the server network protocols you need (TCP/IP, Named Pipes) via the SQL Server WMI/SMO provider, then restart the database engine. The SMO WMI provider is the supported API for this. (learn.microsoft.com)
  • Ensure SQL Server Browser is started and set to automatic when you have named instances (clients rely on it to map instance->port). Service names are MSSQLSERVER (default) or MSSQL$InstName for named instances; the browser service is SQLBrowser. (documentation.help)
  • If you require SQL (mixed) authentication, switch LoginMode to mixed (value = 2) using xp_instance_regwrite or the registry, then restart the SQL service. Set an sa password first (do not enable mixed mode without a secure sa). (learn.microsoft.com)
  • Open the right firewall ports / app exceptions (TCP port for the engine, UDP 1434 for Browser) with netsh or PowerShell (run elevated). (learn.microsoft.com)

Example VB.NET skeleton for a custom action (requires references to SMO/WMI assemblies and admin elevation):

Imports Microsoft.SqlServer.Management.Smo.Wmi
Imports Microsoft.SqlServer.Management.Sdk.Sfc
' run this as admin from your installer custom action
Sub ConfigureSqlForRemote(instanceName As String)
  Dim mc = New ManagedComputer()
  Dim host = Environment.MachineName
  Dim tcpUrn = $"ManagedComputer[@Name='{host}']/ServerInstance[@Name='{instanceName}']/ServerProtocol[@Name='Tcp']"
  Dim tcp = CType(mc.GetSmoObject(New Urn(tcpUrn)), ServerProtocol)
  If Not tcp.IsEnabled Then
    tcp.IsEnabled = True
    tcp.Alter()
  End If

  ' restart the engine service (MSSQLSERVER or MSSQL$Instance)
  Dim svcName = If(String.Equals(instanceName, "MSSQLSERVER", StringComparison.OrdinalIgnoreCase), "MSSQLSERVER", "MSSQL$" & instanceName)
  Dim svc = mc.Services(svcName)
  If svc.ServiceState = ServiceState.Running Then svc.Stop() : svc.Refresh()
  svc.Start()
  ' start SQL Browser if needed:
  Dim browser = mc.Services("SQLBrowser")
  If browser.ServiceState <> ServiceState.Running Then browser.Start()
End Sub

Important cautions and troubleshooting

  • The custom action must run elevated (UAC) and as a user with local admin/sysadmin rights; SMO/WMI operations require that. (learn.microsoft.com)
  • Add firewall rules with netsh (or PowerShell/NetFirewall cmdlets) for the chosen TCP port and UDP 1434; named instances often use dynamic ports unless you set a fixed port. Test with netstat/PortQry from the client. (learn.microsoft.com)
  • AV or third‑party firewalls can still block installs or runtime — test on representative client images and include rollback/logging in your installer to help support.

This gives a repeatable, automatic post‑install path while keeping the preferred option (install-time configuration) available if you can change the SQL Express prerequisite step.

Recommended Answers

All 9 Replies

Member Avatar for 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\"
Member Avatar for 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?

Member Avatar for 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;
commented: cool but I don't thik OP will get it. +11
Member Avatar for 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 ?

Member Avatar for 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.

Member Avatar for 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
:)

Member Avatar for 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!

Member Avatar for 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?

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.