Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Credentials - Mixed authentication or not, that is the question.

Posted on 2014-03-06
7
Medium Priority
?
239 Views
Last Modified: 2014-03-21
Got a topic for discussion,  I've developed an app in VB.Net, it uses SQL (any version should be compatible).
Currently I connect without any specific credentials, just using windows authentication.  Which works fine as long as the SQL is installed locally.  My app also creates a separate SQL instance, the database and several tables.
Currently the app is a clickonce installation, which as you may know runs under each user login separately.  So if another user would login to the desktop (Windows versions), they would have to install the app again. (That will be changed later, with a setup app). The problem is that the SQL may have been installed under the other user login, so the second user has no access to SQL, Also if the user does not have SQL installed they need to get a copy of express & install it manually.
Here where things get fuzzy.  If I give the user instructions on setting up SQL, should I suggest Mixed Authentication and setup my connection string with a user name and password?  That would solve access to SQL for all users logging in individually, but then what if they already have a version of sql installed?  Can they change it to mixed?  Would that possibly cause issues with other apps that may use SQL?
Then there's the issue of SQL being installed on an other Workstation or Server.  My app can browse for any SQL server & instance, but again if my connection string username & password aren't setup on the SQL Server my app will not work.

So what would some recommendations be?  Trying to appease the majority of clients.
0
Comment
Question by:ktaczala
  • 4
  • 3
7 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39910625
Do the users share the data? or each one is working with his own data that just happens to be in central database?
0
 
LVL 13

Author Comment

by:ktaczala
ID: 39910660
Shared data,  it's a document scan and archive app.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39910696
> The problem is that the SQL may have been installed under the other user login, so the second user has no access to SQL,

The solution is probably to grant access to all users at once (or to some security group where all users who can install this application belong), rather than just to the one who installed.
0
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.

 
LVL 13

Author Comment

by:ktaczala
ID: 39910982
>vaadimrapp1<
So ,you suggest NOT to attach username and password to the connection string? And DO NOT configure SQL in a Mixed Authentication Environment.

Can I still add LOGIN and USERNAME to a SQL instance if it's only  windows Authentication?  I ask because, I updated my development version to add them, but can I connect to a SQL server if I don't have the sa account info or the windows login is from a different user or SQL is on another Computer?
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 1000 total points
ID: 39911420
> So ,you suggest NOT to attach username and password to the connection string?

Yes, but this is only one way. Another is to  create single sql server logon and password just for this application, provided that it stays hardcoded inside the application. During setup, the client runs script that creates this logon and password on sql server. What you choose depends on requirements, for example whether audit is required, i.e. to see which user has done what in the database.

> Can I still add LOGIN and USERNAME to a SQL instance if it's only  windows Authentication?

what do you mean by "add"? if you mean the connection string, then no,

>  can I connect to a SQL server if I don't have the sa account info or the windows login is from a different user or SQL is on another Computer?

If it's integrated authentication only, then you can connect only if your windows username or security group it belongs to is permitted to logon to sql server.


From http://msdn.microsoft.com/en-us/library/ms165636(v=sql.105).aspx :

During setup of SQL Server Express a login is added for the BUILTIN\Users group. This allows all authenticated users of the computer to access the instance of SQL Server Express as a member of the public role. The BUILTIN\Users login can be safely removed to restrict Database Engine access to computer users who have individual logins or are members of other Windows groups with logins.

So, if public role is enough, then it all should work. You can always grant all necessary permissions in your database to the public, if that's OK.
0
 
LVL 13

Author Comment

by:ktaczala
ID: 39911644
I need to do some more testing on this issue,  Over the weekend I'll set up some test workstations, individual , workgroups and domain.  I'll let you know my conclusions, and or issues.

>vadimrapp1
Thanks for the good info. (Didn't know that BUILTIN\Users got added by default)
0
 
LVL 13

Author Closing Comment

by:ktaczala
ID: 39946856
After digging into this more, I decided to not to worry about adding the user & password to my connections string.

As per vadimrapp1's replies the built-in/users account is good enough for the home user, for a domain environment there's a little more work but nothing that has to be done in the app.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
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…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

877 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