Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I need to install a SQL Server Express instance (as an admin) which can be later on read and modified by regular users.

I run into http://msdn.microsoft.com/en-us/library/ms254504.aspx, and I'm trying to make it work like it says there, with this connection string:

Provider=sqloledb;Data Source=.\\DUBOCALC;Integrated Security=true;User Instance=true;AttachDBFilename=|DataDirectory|\\" + HelperClasses.DuboCalcSettings.dbname + ".mdf;Initial Catalog=DUBOCALC;Trusted_Connection=yes;

The problem I have is that the instance is created under C:\Program Files\Microsoft SQL Server\MSSQL10.INSTANCENAME, but all this under the Administrator's folder, so regular users can't have access there, where the .mdf files are.

In my bootstrapper file, I have the following line to install SQL Express:

<Command PackageFile="SQLEXPR32_x86_ENU.EXE"
         Arguments='/q /hideconsole /action=Install /features=SQL /instancename=DUBOCALC /enableranu=1 /sqlsvcaccount="NT Authority\Network Service" /SQLSYSADMINACCOUNTS="Builtin\Users" /skiprules=RebootRequiredCheck'
         EstimatedInstalledBytes="225000000"
         EstimatedInstallSeconds="420">`

How could I do that, please?

I think it's a very common use, to install a SQL Server Express instance as admin, then running it as a user, but I can't find any useful information which will help me get this working.

thanks a lot everybody in advance, Manuel.

share|improve this question
Can I, btw, specify the correct permissions for users (BUILTIN\Users) but for the different localizations? Apparently my installer works fine for English systems, but for example not for dutch ones – noloman Feb 3 '12 at 15:11

migrated from stackoverflow.com Feb 1 '12 at 14:53

2 Answers

up vote 3 down vote accepted

/SQLSYSADMINACCOUNTS adds users and groups to the SQL Servers sys admin group (all powerful). That sys admin group is a sql server level security concept.

SQL Server and SQL Server Express runs as a service (and what it runs as does not dictate end user security). Therefore, permissions on data directory (and the .mdf files) does not control how end users connect.

Your connection string should look something like:

Data Source=computerName\SQLExpress;Initial Catalog=myDataBase;Integrated Security=SSPI;

By default SQL Express is installed under a named instance called "SQLExpress" so the data source is the machine name where the service is running and then \ instance name. Integrated security tells the client to connect with it's current credentials. If that user is in the group that has sys admin role membership, it will be powerful.

Here's a link with more connection string samples:

http://www.connectionstrings.com/sql-server-2005

You can also use sql accounts (not integrated) and then embed those in the connection string: c# 2008 SQL Server Express Connection String

share|improve this answer
interesting, but how do I find the computerName? I guess it will be a variable that will change depending on which computer this is being installed in, right? And the SQLExpress and myDataBase string could be the same, for my particular case, I think – noloman Jan 31 '12 at 12:30
You have to provide a bit more info about your architecture. Do you have a DB machine that multiple users using your thick C# app connect to? Or is it a web site and users connect to the web site? Is it an app that only runs on that machine where the database is? – bryanmac Jan 31 '12 at 12:35
I install a local SQL Express 2008 instance, where my app will create a DB (as admin) and when logging off and logging in as normal user, that user should be fully able to manipulate that DB created by the admin during the installation. I'm using a setup project for my app to be installed in the computer by the way. – noloman Jan 31 '12 at 12:44
For example, if it's always an app running on that machine that access a local SQL Express db then you can either hardcode ".\SQLExpress" (dot means local) or your can compute Environment.MachineName. If app is remote then its either (1) config setting (2) user inout (connect dialog) or (3) active directory integration or some other authority/directory to find it. – bryanmac Jan 31 '12 at 12:44
OK - then .\SQLExpress will work. Another option is Compact Edition which doesn't have a service footprint - it's just a file. Express is probably better if you have folks ever concurrently logged on accessing it but not sure about access patterns. blogs.msdn.com/b/sqlservercompact – bryanmac Jan 31 '12 at 12:48
show 1 more comment
up vote 0 down vote

You have to edit the user that SQL Server service is running under. You can do that on the services.msc tab

share|improve this answer
yes, but the problem is that my customer wants that done automatically, with the installer. That's why I have the argument /SQLSYSADMINACCOUNTS="Builtin\Users, but it doesn't work. – noloman Jan 31 '12 at 12:12
/SQLYSADMINACCOUNTS does not control what the service runs as and what the service runs as has nothing to do with end user security access assets through the service. – bryanmac Jan 31 '12 at 12:21
@bryanmac thanks, then that string won't work there, I will remove it, but what should I do then? – noloman Jan 31 '12 at 12:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.