?
Solved

SQL Server Authentication

Posted on 2011-02-22
8
Medium Priority
?
438 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 668 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 1332 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
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 1332 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

771 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