SQL Credentials - Mixed authentication or not, that is the question.
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.
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.
Vadim Rapp
Do the users share the data? or each one is working with his own data that just happens to be in central database?
ASKER
Shared data, it's a document scan and archive 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,
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.
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.
ASKER
>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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
>vadimrapp1
Thanks for the good info. (Didn't know that BUILTIN\Users got added by default)
ASKER
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.
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.