KnowledgeWare
asked on
SQL Windows Authentication vs Mixed Mode - how many use each?
Can anyone guesstimate what percentage of full SQL Server 2005 and later installations are using Windows Authentication vs Mixed Mode?
An application that uses SQL Server 2008 Express and that requires mixed mode - can it run on the same server with full SQL Server without issue? (I'm assuming IT wouldn't switch to mixed mode just to load the app).
TIA
An application that uses SQL Server 2008 Express and that requires mixed mode - can it run on the same server with full SQL Server without issue? (I'm assuming IT wouldn't switch to mixed mode just to load the app).
TIA
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I personally like my logins to have as little access as possible, and that means SQL only. No way for a db login embedded in website code to get access to anything but the website db since its not a user on the box.
If you have access to the box you can easily switch between modes temporarily
http://www.cyberciti.biz/faq/howto-enable-mixed-mode-authentication-in-mssql/
If you have access to the box you can easily switch between modes temporarily
http://www.cyberciti.biz/faq/howto-enable-mixed-mode-authentication-in-mssql/
ASKER
Thanks folks for those ideas....anyone have any estimate percentage-wise of who uses Windows Authentication vs Mixed Mode?
Since the default is windows, I bet the percentage is high that direction
ASKER
Thanks for those comments... I'm sure I'm breaking a fundamental experts rule by going off an a tangent here..but I will ask anyway. Does anyone know where I can find information on whether or not different SQL Express / SQL (full) Versions are compatible? I'm installing a commericial software application for the first time with SQL Express, and need to be concerned about the full range of possible incompatibilities. Where best to find that type of info? TIA
Ms has a page somewhere. Basically express has a 10gb size limit, can't be a replication source. It does everything else.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
@aarontomosky
A windows authenticated user doesn't have to be a user on the box - just a valid user in the domain.
In many ways its more secure - strength, complexity, aging is the default.
In newer SQL that can be turned on as well, but for most SQL accounts its all off.
BTW, did you know that in older versions of SQL, if you install a case insensitive server collation, the SQL passwords are now case insensitive! Whoops!
Besides, when a AD account starts to age out, it warns the users, what happens if aging is turned on for a SQL account, and what happens if it times out without a password change? The infrastructure team is used to changing those passwords and resetting them. What happens to the SQL ones?
In AD the expectation is one user one account. Is that the same in SQL? How many use users that one SQL account?
Regards
David
@aarontomosky
A windows authenticated user doesn't have to be a user on the box - just a valid user in the domain.
In many ways its more secure - strength, complexity, aging is the default.
In newer SQL that can be turned on as well, but for most SQL accounts its all off.
BTW, did you know that in older versions of SQL, if you install a case insensitive server collation, the SQL passwords are now case insensitive! Whoops!
Besides, when a AD account starts to age out, it warns the users, what happens if aging is turned on for a SQL account, and what happens if it times out without a password change? The infrastructure team is used to changing those passwords and resetting them. What happens to the SQL ones?
In AD the expectation is one user one account. Is that the same in SQL? How many use users that one SQL account?
Regards
David
For people that login to the server I totally agree. For apps that need a db connection and the connection string is in the web config with password, I prefer to use a SQL only login as that could be compromised if the site is compromised.
ASKER
Thanks all for useful comments...I've split up the points.
Thanks for the points split, but can we have some indication as why a grade of C?
ASKER
Well C is average, so not bad and I appreciate all comments, however I still feel stuck on whether or not the average DBA would switch their SQL from Windows Authentication to mixed mode to meet the requirements of our app ( I doubt it, I wouldn't) and whether multiple instances of SQL / SQL Express can be run on the same machine...still not sure on these things.
You can run as many stances as you want. And each instance can be mixed or not.
Hi,
The grading guidelines are here
https://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=26
I'd like to point to the last line
>>It is customary to explain any grade that is not an A.
This is something you didn't do ...
Regards
David
The grading guidelines are here
https://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=26
I'd like to point to the last line
>>It is customary to explain any grade that is not an A.
This is something you didn't do ...
Regards
David
ASKER
OK I stand corrected, thanks! I assume its too late to change now....
Hi KnowledgeWare,
Do you see the RequestAttention button?
Clicking on it will open a question in community support with a link back to here which a moderator will see.
Ask something like I'd like to change a grade since an expert asked and blah blah blah. The moderator generally reopen the question so you can then reclose it again with a different grade etc.
Thanks
David
Do you see the RequestAttention button?
Clicking on it will open a question in community support with a link back to here which a moderator will see.
Ask something like I'd like to change a grade since an expert asked and blah blah blah. The moderator generally reopen the question so you can then reclose it again with a different grade etc.
Thanks
David
ASKER
OK David I will do that, thanks for the tip, sorry!
ASKER
"You can run as many stances as you want. And each instance can be mixed or not. "
Thanks Aaron - that's good info.
Thanks Aaron - that's good info.
>> the average DBA would switch their SQL from Windows Authentication to mixed mode to meet the requirements of our app
Depends on your app...
If it is mission critical then the Business can apply pressure, but, normally it would be wrong to assume that a DBA would change Authentication mode.
Small business, more likely (but less likely to understand), Large business a lot less likely.
There is a LOT more to this discussion now that you have indicated that you are the owners of an app and looking for distribution / deployment strategies
First question is, how much interaction and management of the actual database do you expect the business to have to do ? Does your app offer any kind of DB tools ?
Depending on what DB features you are really looking for, and remember if you are developing the app, and if by chance the customer doesnt have SQL Server, then the cost of your app goes up by quite a bit if you deploy with features other than Express. Then we need to understand its use - multi-user enterprise wide, or more single user.
Unless I read too much into your comment above... If you are the developer and looking for deployment strategies then there is a lot more to discuss and to discover about what you are doing (not what the app does as such).
Depends on your app...
If it is mission critical then the Business can apply pressure, but, normally it would be wrong to assume that a DBA would change Authentication mode.
Small business, more likely (but less likely to understand), Large business a lot less likely.
There is a LOT more to this discussion now that you have indicated that you are the owners of an app and looking for distribution / deployment strategies
First question is, how much interaction and management of the actual database do you expect the business to have to do ? Does your app offer any kind of DB tools ?
Depending on what DB features you are really looking for, and remember if you are developing the app, and if by chance the customer doesnt have SQL Server, then the cost of your app goes up by quite a bit if you deploy with features other than Express. Then we need to understand its use - multi-user enterprise wide, or more single user.
Unless I read too much into your comment above... If you are the developer and looking for deployment strategies then there is a lot more to discuss and to discover about what you are doing (not what the app does as such).
ASKER
Hello Mark - thanks for those comments. It's not a mission critical app but does contain important (and private) data. The 'front end' makes any required updates to the database, the user is not expected to perform maintenance, and we include no DB tools. We would prefer they not touch the back end. The database is for a small team of users (2-10 typically, could be more, but never high numbers). My concerns revolve around deploying this responsibly into a wide range of small-medium business which will have anywhere from no experience with SQL, no IT person, to top level DBA's with full knowledge of SQL.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all for those comments!
This is a desktop (not web) application with an MS Access 2010 front end. The SQL Express installer and a data converted (access to SQL) is included (distributed with app).
The front end has a user login/password that controls what modules the user can access - that is now access to tables are controlled. The SQL login/password is hard coded and not visible to the user unless they somehow break into the code, however this is unlikely as there are much better apps to hack. This app is intended for small to medium companies and they may or may not have a knowledgeable IT person.
This is a desktop (not web) application with an MS Access 2010 front end. The SQL Express installer and a data converted (access to SQL) is included (distributed with app).
The front end has a user login/password that controls what modules the user can access - that is now access to tables are controlled. The SQL login/password is hard coded and not visible to the user unless they somehow break into the code, however this is unlikely as there are much better apps to hack. This app is intended for small to medium companies and they may or may not have a knowledgeable IT person.
Now, that last post looks like great background for a new question in the Access + SQL + MS Development TA's - It is different enough from the original question here, so, well worth asking.
May as well gather as much expert advice as you can, and there are commercial developers / product suppliers in the Access TA that have probably had the very same issues...
May as well gather as much expert advice as you can, and there are commercial developers / product suppliers in the Access TA that have probably had the very same issues...
ASKER
Thanks to all!
ASKER
Thanks to all...sorry I messed up the scoring!
if you install different SQL Server versions (like 2005 and 2008) they can live beside each other, no problems. I never tried to install an Express together with a full version of the same version, I guess that will not be possible as they would use the same services and overwrite each other. The installation requirements will be tested by the installer and I guess it will find an existing version and warn you.
But as you say your IT will not switch to mixed mode only to install an app I would guess they would of course not install an Express version on the same server. Maybe you have another server where you can install the Express version or you install a VM on the database server where you can install the Express version without disturbing the host server.
Cheers,
Christian