Distributing database with application?

Question

Distributing database with application?

Job Lot

Old Hand

Points: 318
More actions
September 28, 2004 at 8:03 pm

#163213

I have developed an application which creates financial plans for our customers. I am using SQL Server 2000 as backend, with various stored procedures. Now I want to distribute the application to the customers with their respective plans. How would I distribute the database along with the application? Should I use Access or MSDE?

If I use MSDE, how would it be installed on client system, how would the tables and stored procedures be created, and how would client relative data get populated in the tables?

If I choose Access, then how would my store procedures work?

Please edify. Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

MSDE is cut-down SQL Server and I'm therefore sure that you should use that if you can. Moving to Access will create a lot of rework - eg stored procedures are not supported and the application is not true client/server, so the whole application architecture is different.

The first thing to do will be to install MSDE on the target system, using the standard MS install. I do not know about the next bit - getting your db attached to the installed MSDE instance - but someone else here will ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thorbjörn Eriksson SSC Enthusiast Points: 121 More actions · Answer 2

We're having a similar situation as you describe. We decided to go for MSDE and our solution is to create a production-ready database, detach it using sp_detach_db.

We then distribute the database files (.mdf and .ldf).

The installation program then installs MSDE on the target machine, copies the two database files to where we decided to store them and calls a small C++ program to attach the database files.

The C++ program uses ADO to call the 'sp_attach_data' stored procedure and performs some initialization of the database that goes into the master database (like setting up some logins)

greg-170464 SSC Journeyman Points: 89 More actions · Answer 3

we use installshield to install MSDE and then shell out to a vb app that restores a backup of our database and our application.

Job Lot Old Hand Points: 318 More actions · Answer 4

What switches I need to provide and how, in order to install MSDE on client machine? sorry i am new to all this!

thanks

Thorbjörn Eriksson SSC Enthusiast Points: 121 More actions · Answer 5

Read this article on MSDN:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/distsql/distsql_84xl.asp

In our case we've downloaded and unpacked the MSDERelA packaged from Microsoft. Then we include all those files in our installer (we use Wise InstallBuilder). On the client machine those files are copied to a temporary folder (keeping the directory structure which are required by the MSDE setup).

We then call setup.exe from the installer with the following parameters:

setup /L*v logfile.txt /settings settings.ini TARGETDIR="C:\Program files\application name\" DATADIR="C:\Program files\application name\" SAPWD="something"

Our settings file (settings.ini) contains:

[Options]

SECURITYMODE=SQL

DISABLENETWORKPROTOCOLS=0

INSTANCENAME="Application name"

COLLATION="Finnish_Swedish_CS_AS"

The parameters (roughly) means:

/L*v : installation log filename

/settings : read file for more installation params

TARGETDIR=:where the MSDE binaries should go

DATADIR=:where the database data/log files should reside

SAPWD=BMS administrator (sa) password.

SEQURITYMODE=:How you want MSDE to authenticate your users

DISABLENETWORKPROTOCOLS: If you want the DBMS to be accessible over a network.

INSTANCENAME=:A machine can have 16 instances of MSDE running at the same time, you should probably give your instance a name to avoid it to interfere with some other application's instance.

COLLATION=:sort order rules; MSDE is by default installed using a collation that is case insensitive - it makes no difference in upper/lower case letters which we needed, hence the 'CS' (Case Sensistive) part of the collation name.

I hope this example can give you a hint, otherwise there are some more at MSDN.

Job Lot Old Hand Points: 318 More actions · Answer 6

Hi Thorbjörn,

Thanks a ton for the information. Helped me a lot. Have couple of questions though

How do you embed MSDE with your installation program? I read about merge modules but they aren’t available with MSDE 2000 ReleaseA.

Secondly, client can easy register an instance of MSDE using any third party GUI or EM and could get hold of our data structure. How can I over come this issue?

Thanks

Thorbjörn Eriksson SSC Enthusiast Points: 121 More actions · Answer 7

It seems like my last reply went into deep space, so if this reply turns up twice you know why.

Microsoft states that it's not recommended to use merge modules as an instance installed using merge modules can't be patched. Instead you should use setup.exe in the MSDERelA package.

As I wrote in my previous answer, we use a tool, Wise InstallBuilder to create our installer .exe. In this tool we specify which files we want to distribute. If you have unpacked the MSDERelA package and had a look at it, it contains some .msi files, setup.exe and some other stuff. We've put all those files in our installer .exe using InstallBuilder, and a commandline that runs setup.exe.

Regarding your other question, I'm not really sure I understand what you mean. If you dont want anyone to be able to manipulate your data or be able to see your database structure, you should hide your 'sa' password. This is set by setup.exe (SAPWD=) and if you call setup.exe from within an installer program, no one needs to see it.

To prevent anyone using an ordinary user's login to access the database through another tool, you can use application roles. Take a look at:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q308312

And to get the whole story you can check out:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec01.mspx

Job Lot Old Hand Points: 318 More actions · Answer 8

I am still confused, let me put it this way:

I am distributing my application which includes MSDE 2000. My setup program attaches .MDF and .LDF files once instance of MSDE 2000 is installed on the customers computer. Now my worry is how can I restrict customers running W2k or XP from accessing my database using EM or third party GUI’s. If customers are running W2k or XP and have administrative privileges they can easily register the installed instance of MSDE with EM and access the data structure, stored proc etc?

I tested it on a computer which is running W2k, I logged in as Administrator, ran my Setup program to install MSDE, attach .MDF and .LDF, .NET framework, MDAC and my application. Then I installed EM and registered an instance of installed MSDE with it. I could access everything in the attached db.

How can I prevent this, as data structure, stored proc etc means a lot and can’t let anyone steal it.

Thanks

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

I'm not an MSDE expert, but I would have thought that to gain full access to the MSDE instance from EM, you would need admin username (sa) and password for the instance. As Thorbjörn said, you can set this as part of running the setup program. Or am I missing something?

Regards

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Job Lot Old Hand Points: 318 More actions · Answer 10

Phil

I have already set "sa" password for the instance. But if you install MSDE on W2k or XP, it enables windows authentication. There are only two modes available either windows authentication and windows authentication or sql authentication.

I am using a separate machine with W2k installed on it for testing my setup program. If I am logged to Administrator account and register instance of installed MSDE with EM it doesn’t prompts for any sa password. It simply registers the instance and database is fully accessible. I am wondering what is happening to sa authentication?

Thorbjörn Eriksson SSC Enthusiast Points: 121 More actions · Answer 11

Ok, I missed that MSDE uses its 'mixed mode authentication' which means that administrators on a windows machine automatically get access to the DBMS. But you should be able to turn of this by removing the BUILTIN\Administrators login from the sysadmin group or remove it (I haven't tried this myself, I got it from

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22)

In the FAQ I referred to above I noticed another potential problem: If someone just copies your .ldf & .mdf files and attaches them to another instance of MSDE it should be possible to access both data and stored procs. Here I'm (again) on thin ice, but I think that the only way to protect your database is to use some third party tool that can encrypt your entire database. Maybe

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=30

can be of any help? Look under Encryption.

/Tobbe

Job Lot Old Hand Points: 318 More actions · Answer 12

removing the BUILTIN\Administrators works well.

Out of curiosity, do you know how I can customize my setup program in order ascertain instance name (machinename\myInstanceName) on client’s machine and run sp_attach_db proc.

Thanks

Thorbjörn Eriksson SSC Enthusiast Points: 121 More actions · Answer 13

In our case, we set the instancename (INSTANCENAME=) in the .ini file used by MSDE's setup.exe, but I think you can use the INSTANCENAME parameter as a commandline param to setup.exe too. I don't believe there is any other ways of doing it.

To attach the databasefiles we have written a piece of software (in C++, a little bit messy) that we call from the installer. It uses an ADO Commandobject to run the stored procedure. The procedure call is "sp_attach_db @dbname = (databasename), @filename1 = '(Path to .mdf file)', @filename2 = '(Path to .ldf file)'"

Use your favourite language and get a ADO connection object and a ADO command object. Set the commandobject's parameters 'CommandText' to the procedure call above,

'CommandType' to adCmdStoredProc and 'ActiveConnection' to the connection object. Then run the commandobject's ExecuteNoRecords -method. If you can manage VB there is an example on how to run a stored procedure using ADO on

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconcommandstoredproc.asp

If you distribute the 'osql' utility you can use it directly to execute the procedure call, but i'm not sure that you are allowed to do so, as 'osql' is not a part of the MSDE distribution.