Solved

SQL Server Authentication

Posted on 2011-02-22
8
426 Views
Last Modified: 2012-06-21
I am planning to distribute SQL Server 2008 R2 Express by silently installing it along with my application (in .NET Framework 4.0).

In the installation, I will set  SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS".

If the user logs in (at windows) as an administrator, then I could connect to SQL Server through windows authentication. But if the user logs in as a standard user, how will my app connect to SQL Server?
0
Comment
Question by:Thomasian
  • 4
  • 3
8 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 167 total points
ID: 34957866
It won't.

Best practice is to run the application under the credentials of a pre-determined domain user (i.e. setup a new user specifically for this application) and give it the rights it needs to run on the SQL side. Then, you can handle individual user authentication within the application itself.

Another option is to setup a SQL authentication login that has the necessary rights and connect to the instance with that info.

0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 333 total points
ID: 34957894
Yes, as stated above, its recommended to have SQL Server services starting using Domain user with admin privilege in local machine in order to access Network shares and paths..

Since I answered your earlier question and aware of your scenario that you want to distribute SQL Server along with your application, you don't have any idea on the domain user name for your clients where your application would be installed. Hence the best approach for your scenario would be to:

1. Set SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" during installation.
2. Give a guide/manual with steps to change Startup account of SQL Server services to a domain user (in their domain)
3. Give steps to add additional Sysadmin accounts to your SQL Server instance

PS: Since you would have sa password hardcoded with the application of your choice, just mention that in the manual and ask them to change it of their choice.

And hope sa login is not used for your application and if so, then it a bad practice and change it to some other login with required privileges.
0
 
LVL 22

Author Comment

by:Thomasian
ID: 34958075
My app previously only uses sql server ce 3.5 as backend db, so the service won't really be accessing any network resources. I only switched to sql server express to add client reporting features.

In my case, will running the sql server service as "Local System" be enough?

Can I just automatically add sysadmin accounts through a script? I would really like to keep it as simple as possible for the users.

i.e. When I had sql server ce 3.5 as backend, the user just needs to run a single installer and everything is up and running.

Just one other thing, SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" doesn't seem to work (at least not how I expected). I logged in on windows as an administrator, but when I used windows authentication in management studio, I was not given administrative rights. I tried logging in as sa to check, and I can see that there is a login of BUILTIN/ADMINISTRATORS with server roles of sysadmin.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 22

Author Comment

by:Thomasian
ID: 34958143
Ok,  I got the "BUILTIN\ADMINISTRATORS" question. I have to start management studio with "Run as Administrator" for it to work.
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 333 total points
ID: 34958295
>> I have to start management studio with "Run as Administrator" for it to work.

This is not an issue with either SQL Server or BUILTIN\ADMINISTRATORS but has to do with UAC in your server. If you disable it, you would be able to get it work without any issues.

>> Can I just automatically add sysadmin accounts through a script? I would really like to keep it as simple as possible for the users.

you can do it via Powershell scripts but it would be recommended to do it manually since the individual instances of your application would be having their own sysadmin peoples right..
0
 
LVL 22

Author Comment

by:Thomasian
ID: 34958330
Only 1 instance of my app will be running on a pc.

But just to extend my knowledge, why does each instance need to have their own sysadmin rights? Can't they just all use the same user/pass?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34958367
>> But just to extend my knowledge, why does each instance need to have their own sysadmin rights? Can't they just all use the same user/pass?

Sorry for the confusion..
I meant instance here as the installation of your application along with SQL Server in a particular client machine. This client might have some domain with them and is not the case with your second client who might install your application along with SQL Server..

For the basic installation, you can provide the same username and password and give sysadmin rights to BUILTIN\ADMINISTRATORS
0
 
LVL 22

Author Closing Comment

by:Thomasian
ID: 34958380
Thanks for all your help :)
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question