Setup of SQLExpess Options

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matt F - 12 Jan 2007 18:43 GMT

I have a setup/deployment package that I've included SQL Express, .Net
Framework 2.0, and MSI installer as pre-requisites. The project is created
in Visual Studio 2K5, Professional if that matters.

There are several options for SQL Express that I need to handle.
1: Install to a specific instance.
2: Enable TCP/IP
3: Set security mode to SQL

I've looked around and just can't seem to find what I need to do in order to
handle these items. Any help would be greatly appreciated.

Reply to this Message

Steven Cheng[MSFT] - 15 Jan 2007 05:27 GMT

Hello Matt,

From your description, you're developing a setup program for your
application, you've included the SQL Express as pre-requisites, currently
you're wondering how to programmatically do some initial configuration on
the sql express instance during the setup process, correct?

Based on my understanding and research, here are some suggestion to the
three questions you mentioned:

1: Install to a specific instance.
=================================
I'm afraid this is limited by the SQL Express edition because SQL Express
is always forced to be installed as a fixed named instance
(machine\SQLExpress). We can not customize this instance's name.

2: Enable TCP/IP & 3: Set security mode to SQL
=================================
There are a couple of ways to for programmatic configuration of SQL Server
2005 instance:

**Write a VBscript/Jscript/VB6/perl (any late bound language) script that
changes the security mode of SQL Server using the WMI Provider.

** Write a VBscript/Jscript/VB6/perl (any late bound language) script that
changes the security mode of SQL Server using SMO COM Friendly Wrappers
Write a C#/VB.NET/[add your favorite .NET Language] program using SMO (it
would in fact use the WMI Provider)

**Write a C program that uses COM to interoperate with DMO9, WMI, or SMO
objects that are registered to COM. Note that the SMO COM wrappers only
support late bound (you can write your own if needed)

I'd prefer the use SMO through .net code(VBNET or C#) since it is quite
convenient to use. And After you installed SQL Express, .net framework 2.0
runtime should be installed also(it is required for SQL SERVER 2005).
Therefore, your custom .net code in setup project(such as custom action)
can work corretly. For example, the following C# code help set SQLExpress's
authentication mode to Inegrated wnidows.

================
Server express = new Server("localhost\\sqlexpress");
MessageBox.Show("current login mode: " +
express.Settings.LoginMode);
express.Settings.LoginMode = ServerLoginMode.Integrated;
express.Alter();
==================

Here are some other web articles introducing configure SQL server protocols
through WMI or SMO managed api.

http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482840.aspx

http://blogs.msdn.com/mwories/pages/change-server-protocol.aspx

#some other samples on using SQL 2005 SMO:
http://blogs.msdn.com/mwories/archive/tags/SMO+Samples/default.aspx

Still another possible way is directly change the registery value. e.g.

===========
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\@Enabled
===========

However, this is not quite recommended and I'd suggest you consider the
former means if possible.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Matt F - 15 Jan 2007 10:29 GMT

Steven,

Thank you for your response. Can you please clarify item 1? You state that
the instance name of SQLExpress cannot be customized, however I have an
instance on my machine now that is most definitely a customized instance
name. The question is only how to automate the install to handle this.

> Hello Matt,
>
[quoted text clipped - 95 lines]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.

Reply to this Message

Steven Cheng[MSFT] - 15 Jan 2007 12:49 GMT

Thanks for your reply Matt,

Yes, for standalone installation, SQLExpress support named instance(as
normal SQL Server installation). My previous reply aimed at the default
installation of SQL Express and the SQLEXPRESS setup in VS 2005. For
standalone install of SQL Express, you can use commandline install and
specify named instance through commandline arguments. See the below blog
article:

#Configuring SQL Express During Installation
http://blogs.msdn.com/sqlexpress/archive/2006/09/20/configuring-sql-express-
during-installation.aspx

And the problem here is that you'll use a VS SETUP bootstrapper package to
install the SQLExpress, and the default SQLExpress seems use the default
"SQLEXPRESS" instance name. Thus, you will need to create a custom
bootstrappe package for SQLExpress. Here is a good MSDN article introducing
how to author a custom bootstrapper package for VS 2005 setup project:

#Authoring a Custom Bootstrapper Package for Visual Studio 2005
http://msdn2.microsoft.com/en-us/library/aa730839(vs.80).aspx

Hope this helps.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Matt F - 16 Jan 2007 06:11 GMT

Steven,

There is a link from the first article you referenced that should be a white
paper describing exactly how to do what I need, but the link is broken:

The link is:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/emsq
lexcustapp.asp

Any idea where I can find this article?

> Thanks for your reply Matt,
>
[quoted text clipped - 29 lines]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.

Reply to this Message

Steven Cheng[MSFT] - 16 Jan 2007 09:06 GMT

Thanks for your reply Matt,

The first article's link you mentioend has been removed from msdn library
since it is a old article published before the visual studio 2005 official
release. That article mainly introduce how to create a custom bootstrapper
package used by visual studio setup project. Now there is updated msdn
article introducing it, that's exactly the second msdn reference I've
pasted:

#Authoring a Custom Bootstrapper Package for Visual Studio 2005
http://msdn2.microsoft.com/en-us/library/aa730839(vs.80).aspx

though it is not specific to SQL Express, it completely introduce how to
author a custom bootstrapper package for VS 2005 setup prerequisits. Also,
you can refer to the built-in SQL Express bootstrapper package under the
following dirctory(under vs 2005 install folder):

C:\Program Files\Microsoft Visual Studio
8\SDK\v2.0\BootStrapper\Packages\SqlExpress\

and you can find the "package.xml" file in the "en" sub dir whicn contains
the commandline arguments info, you can customize it also.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Matt F - 16 Jan 2007 17:53 GMT

Thanks for your replies... yes, I've made a copy of the SQLExpress
bootstrapper package and am working toward modification for my purposes.

I'm having a little difficulty finding any kind of reference for what
command line params exist for the install. Any hints on where to find?

Specifically needed:
Allow tcp/ip access
Specify instance name (actually already have this one from exisging
package.xml)
Set security to be SQL only

> Thanks for your reply Matt,
>
[quoted text clipped - 27 lines]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.

Reply to this Message

Matt F - 16 Jan 2007 18:13 GMT

Another item I'm concerned about needing:

Knowing if the specific instance is already installed... the current sql
prereq seems to simply check whether sqlexpress is installed at all, which
wont' work for me.

So to recap:

I'm working on modifying a copy of the existing sqlexpress prereq:

Need hints for command line params - specificaly:
Allow tcp/ip access
Specify instance name (actually already have this one from existing
package.xml)
Set security to be SQL only

Also need hints for how to handle an installcondition of my instance of SQL
Express is installed.

> Thanks for your replies... yes, I've made a copy of the SQLExpress
> bootstrapper package and am working toward modification for my purposes.
[quoted text clipped - 43 lines]
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.

Reply to this Message

Steven Cheng[MSFT] - 17 Jan 2007 04:56 GMT

Thanks for your followup Matt,

Regarding on your recap on the requests, here is a simple summary on the
current available solutions:

Need hints for command line params - specificaly:
====================
this should be configurable through the <command> element in the
Package.xml file(of vs 2005 bootstrapper package). You can find that the
built-in SQL Express bootstrapper package's package.xml already has
specified some commandline arguments. You can refer to its syntax(or the
custom VS 2005 bootstrapper package article)

Allow tcp/ip access
==================================
The [DISABLENETWORKPROTOCOLS] parameter is used for setting the startup
type of the network protocols. It has the following 3 options:

0 - Shared memory = On, Named Pipes = On, TCP/IP = On

1 - Shared memory = On, Named Pipes = Off (local only), TCP/IP = Off

2 - Shared memory = On, Named Pipes = Off (local only), TCP/IP = On

Specify instance name (actually already have this one from existing
package.xml)
==============================
there is a "[Instancename] parameter you've already found.

Set security to be SQL only
===============================
there is a [SECURITYMODE] parameter you can use for the sqlexpress's setup
program.

You can find all the available command parameters in the following BOL
reference(it is for standard SQL Server 2005, so there has some parameters
not workable for SQL express)

#How to: Install SQL Server 2005 from the Command Prompt
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#installationscripts

Also need hints for how to handle an installcondition of my instance of SQL
Express is installed.
====================================
I think this can be done through VS 2005 bootstrapper package's "Install
Check" and "Install condition" feature(mentioned in the "Authoring custom
bootstrapper package...." article). And for your scenario, if you want to
detect whether there is already an named SQL instance(with the same name as
the one you want to install), you can check the registry for such a subkey.
And the VS 2005 bootstrapper package's "Install Check" contains
"RegistryCheck" by which you can let your bootstrapper installation run
depend on some registry value.

#<InstallChecks> Element (ClickOnce Bootstrapper)
http://msdn2.microsoft.com/en-us/library/ms229432.aspx

Hope the above helps clarify it further.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Setup of SQLExpess Options

Free Magazines