SQL Windows Authentication vs Mixed Mode - how many use each?

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/

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

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

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

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.

>> 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).

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.

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...

ASKER

Thanks to all!