Solved

SQL Server Authentication

Posted on 2011-02-22
8
420 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now