Solved

SQL Server Authentication

Posted on 2011-02-22
8
431 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
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

730 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