Solved

SQL Server Authentication

Posted on 2011-02-22
8
434 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

717 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