September 28, 2004 at 8:03 pm
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
September 28, 2004 at 11:21 pm
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 30, 2004 at 4:58 am
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)
September 30, 2004 at 1:36 pm
we use installshield to install MSDE and then shell out to a vb app that restores a backup of our database and our application.
September 30, 2004 at 9:26 pm
What switches I need to provide and how, in order to install MSDE on client machine? sorry i am new to all this!
thanks
October 4, 2004 at 1:36 am
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.
October 5, 2004 at 5:16 am
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
October 5, 2004 at 9:11 am
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
October 6, 2004 at 9:18 pm
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
October 6, 2004 at 10:27 pm
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 7, 2004 at 2:09 am
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?
October 7, 2004 at 2:31 am
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
October 8, 2004 at 12:35 am
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
October 8, 2004 at 6:10 am
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
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.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply