Installing MSDE on different plattforms.

Product
Our product is developed in Visual Studio .NET using MFC and .NET.
It connects to a MS SQL Server Desktop Edition (MSDE), where the data is
stored.
Installation
Our problems relates to the installation of our product.
Installation happens in these steps:
- Install .NET
- Install MSDE2000 (with a SA password we have chosen)
- Reboot
- Start our own InstallDB.exe which does the follwoing:
-- Connects to the MSDE (the master database)
-- creates our own database (called "exordb" and creates our own sql
user called "exor")
-- Connects again to MSDE using the exor user and loads data into the
exordb database.
We are using the following connectionstring for connecting to master db the
first time.
Data Source=127.0.01; Integrated Security=SSPI;Initial Catalog=master
We are considering to use this one instead:
server=127.0.0.1;uid=sa;pwd=****;database=master
after the exordb is set up we connect using
server=127.0.0.1;uid=exor;pwd=***;database=exordb
Problems
1. (the most critical)
Installation failes (the first connection string) for some customers with XP
SP2 even if they turn off the firewall.
They get the standard SQLServer Exception:
SQL Server does not exist or access denied.
The MSDE is there (unnamed instance) and is ON.
2. (Windows 98)
When installed on Windows98 MSDE does not start automatically after the
reboot, at least that what it seems on the WIndows 98 machine we have tested
on. (We have also discovered that only SQL Autentification works on Win98,
we solved that by using the second connection string instead of the first
one).
3. (manual start of MSDE)
How may we detect (in our code, preferrably .NET/C#) that MSDE is not
running and how do we start it from the code?
4. (MSDE installed by someone else)
If MSDE is already installed on the computer by another program and
SQL-authentification is not set, how may we set it so our connection string
will work.
hi Vilhelm,
Vilhelm wrote:
> Product
> Our product is developed in Visual Studio .NET using MFC and .NET.
> It connects to a MS SQL Server Desktop Edition (MSDE), where the data
> is stored.
> Installation
> Our problems relates to the installation of our product.
> Installation happens in these steps:
> - Install .NET
> - Install MSDE2000 (with a SA password we have chosen)
> - Reboot
> - Start our own InstallDB.exe which does the follwoing:
> -- Connects to the MSDE (the master database)
> -- creates our own database (called "exordb" and creates our own
> sql user called "exor")
> -- Connects again to MSDE using the exor user and loads data into
> the exordb database.
>
> We are using the following connectionstring for connecting to master
> db the first time.
> Data Source=127.0.01; Integrated Security=SSPI;Initial
> Catalog=master
> We are considering to use this one instead:
> server=127.0.0.1;uid=sa;pwd=****;database=master
> after the exordb is set up we connect using
> server=127.0.0.1;uid=exor;pwd=***;database=exordb
> Problems
> 1. (the most critical)
> Installation failes (the first connection string) for some customers
> with XP SP2 even if they turn off the firewall.
> They get the standard SQLServer Exception:
> SQL Server does not exist or access denied.
> The MSDE is there (unnamed instance) and is ON.
I'm trying to get some more material about that kind of issue on XP sp2 to
report it to Microsoft representative... but I am unable to repro it in my
tests.. I'm aware of another issue on XP sp2 which cause installation
failure due to MDAC stack troubles, currently with no workaround as no MDAC
2.8 xp sp2 refresh is available... please forward me more info, if you
like..

> 2. (Windows 98)
> When installed on Windows98 MSDE does not start automatically after
> the reboot, at least that what it seems on the WIndows 98 machine we
> have tested on.
it's correct... MSDE runs as a standard application on Win9x platform as it
does not support service mode..
you should check that the service manager is added in the autostart folder
and that the "Autostart service when OS starts" check box is checked ..
to start MSDE you can call the SQL Server Service Manager as following:
(named instance)
scm.exe -Action 1 -Silent 1 -Service MSSQL$InstanceName
(default Instance)
scm.exe -Action 1 -Silent 1 -Service MSSQLServer
please keep in mind that, on Win9x, MSDE path will not be added to
autoexec.bat if you don't reboot the
computer, so you have to rely on Win32 API like FindFirstFile or the like to
find where the scm.exe is (usually ..\Program Files\Microsoft SQL
Server\80\Tools\Binn\scm.exe)

>(We have also discovered that only SQL
> Autentification works on Win98, we solved that by using the second
> connection string instead of the first one).
MSDE installed on Win9x does not support integrated security

> 3. (manual start of MSDE)
> How may we detect (in our code, preferrably .NET/C#) that MSDE is not
> running and how do we start it from the code?
you can rely on SQL-DMO COM object...
you can then ping it like
dim oServer as SQLDMO.SQLServer2
objServer.PingSQLServerVersion
start it, or just connect...

> 4. (MSDE installed by someone else)
> If MSDE is already installed on the computer by another program and
> SQL-authentification is not set, how may we set it so our connection
> string will work.
you have to manually change the login authentication mode, logging in as
member of the server's sysadmin role and setting the
objServer.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Mixed
or modify the Windows registry
HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
LoginMode=2
(HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer for
a named instance)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you a lot for some useful answers!!
More input on question 1.
I have not been able ot reproduce it here, but some of my customers report
this problem. Maybe we can resolve it with some well-phrased questions I can
ask one of my customers on the phone.
We use installshield Premier edition which installs MDAC 2.8 if necessary as
part of the installation process.
Vilhelm Heiberg
Norway.
"Andrea Montanari" wrote:

> hi Vilhelm,
> Vilhelm wrote:
> I'm trying to get some more material about that kind of issue on XP sp2 to
> report it to Microsoft representative... but I am unable to repro it in my
> tests.. I'm aware of another issue on XP sp2 which cause installation
> failure due to MDAC stack troubles, currently with no workaround as no MDAC
> 2.8 xp sp2 refresh is available... please forward me more info, if you
> like..
>
> it's correct... MSDE runs as a standard application on Win9x platform as it
> does not support service mode..
> you should check that the service manager is added in the autostart folder
> and that the "Autostart service when OS starts" check box is checked ..
> to start MSDE you can call the SQL Server Service Manager as following:
> (named instance)
> scm.exe -Action 1 -Silent 1 -Service MSSQL$InstanceName
> (default Instance)
> scm.exe -Action 1 -Silent 1 -Service MSSQLServer
> please keep in mind that, on Win9x, MSDE path will not be added to
> autoexec.bat if you don't reboot the
> computer, so you have to rely on Win32 API like FindFirstFile or the like to
> find where the scm.exe is (usually ..\Program Files\Microsoft SQL
> Server\80\Tools\Binn\scm.exe)
>
> MSDE installed on Win9x does not support integrated security
>
> you can rely on SQL-DMO COM object...
> you can then ping it like
> dim oServer as SQLDMO.SQLServer2
> objServer.PingSQLServerVersion
> start it, or just connect...
>
> you have to manually change the login authentication mode, logging in as
> member of the server's sysadmin role and setting the
> objServer.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Mixed
> or modify the Windows registry
> HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
> LoginMode=2
> (HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer for
> a named instance)
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>