Solved

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

Posted on 2013-01-25
29
533 Views
Last Modified: 2013-02-12
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
0
Comment
Question by:KnowledgeWare
  • 11
  • 5
  • 5
  • +2
29 Comments
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

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
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 375 total points
Comment Utility
Well, I used to have Express and Enterprise SQL2005 on the same machine with no problems.

Named servers ensure you connect to the correct instance.

For my own (dev) machine I used mixed mode on Developer Edition and Windows authentication on the Express edition. But I am also an admin on that machine. Reasons being I needed to test and explore different security issues.

As a developer, I used to always prefer mixed mode so that any patches or upgrades could happen with a known account and higher chance of success without the "dumb" phone calls about an upgrade script not working.

For some installations, we used to have "an embedded DB" type approach so it was pretty much bundled within the app and installed as a seperate service with known accounts and very little user tools (no SSMS / EM) we built the minimum needed within the app.

If I was to rewrite from scratch, it would probably not be fixed accounts, and so, use Windows Authentication. Always hard when the customers are not DB aware, or have no DBA type people in their ranks.

When we ran the installations, we would always check to see if there was an existing server and give the choice (with a bunch of prerequisites).

For other third party applications, you would need to check the apps' connection to see what it really required.

I would say with increased security issues and the need for greater controls, it would be an increasing liklihood that Windows Authentication is becoming more of the standard for the enterprise. Centralised management over a range of (db) connected apps is ceratinly a lot easier to manage via Windows.

And it is MS recommendation to use Windows Authentication whenever possible : http://support.microsoft.com/kb/2028697
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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/
0
 

Author Comment

by:KnowledgeWare
Comment Utility
Thanks folks for those ideas....anyone have any estimate percentage-wise of who uses Windows Authentication vs Mixed Mode?
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
Since the default is windows, I bet the percentage is high that direction
0
 

Author Comment

by:KnowledgeWare
Comment Utility
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
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
Ms has a page somewhere. Basically express has a 10gb size limit, can't be a replication source. It does everything else.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 375 total points
Comment Utility
Express and "full" have the same engine. Anything you do in Express will be fine in the Full version (but not necessarily the other way around). Express is throttled back with a few more constraints (CPU, Memory, Size of DB).

You can detach an Express database and attach to a full server (but again, not the other way around).

You can read the "features" comparison : http://msdn.microsoft.com/en-us/library/cc645993.aspx

When you go through the link, have a look at the types of things being supported by each edition and decide if they are important for you.

Also remember that if you are the developer, there are license implications if you do write code that depends on full version features, where as Express is free (and localdb can be essentially embedded).

There is also the Developer Edition which is the same as Enterprise, but designed just for developers to be able to write their code, test etc...
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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.
0
 

Author Comment

by:KnowledgeWare
Comment Utility
Thanks all for useful comments...I've split up the points.
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Thanks for the points split, but can we have some indication as why a grade of C?
0
 

Author Comment

by:KnowledgeWare
Comment Utility
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
You can run as many stances as you want. And each instance can be mixed or not.
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi,

The grading guidelines are here
http://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
0
 

Author Comment

by:KnowledgeWare
Comment Utility
OK I stand corrected, thanks! I assume its too late to change now....
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
0
 

Author Comment

by:KnowledgeWare
Comment Utility
OK David I will do that, thanks for the tip, sorry!
0
 

Author Comment

by:KnowledgeWare
Comment Utility
"You can run as many stances as you want. And each instance can be mixed or not. "

Thanks Aaron - that's good info.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
>> 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).
0
 

Author Comment

by:KnowledgeWare
Comment Utility
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.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 125 total points
Comment Utility
Hi Knowledgeware,

If the app is used by a small number of users, can those users access the entire application? If it is segmented or partitioned in anyway, how is this achieved? As in, can this occur with restricting access to tables, is is it finer grained than that.

Are these small number of users fairly IT knowledgable? That is, if they stumble across say the SQL login/password, could they using Excel database query to connect to the database and access the data? If its possible they'll authorised or not use other tools to access or report on the data, then access needs to be governed by the database, rather than the application.

If all users access the entire application, then its just as easy to set up a domain group, add users to that group, then grant that group data_reader/data_writer roles in the database.

On the other hand, if all database access is via stored procedures, then execute permission on those procedures may be all that is required.

Since if SQL is installed, it _will_ have windows authentication, (and that's stronger/better/brighter etc) do try to use that. But anonymous web users generally easier to use a SQL Authenticated account.

Does this clarify things?

Regards
  David
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 375 total points
Comment Utility
Are you installing or distributing Express with your App, or, are you simply including download notes and stating SQL Server as a pre-requisite (ie they have to install themselves) ?

Then there is your connection strategy. Basically for Windows Authentication, you use "Integrated Security=SSPI" rather than the username and password. More complete is to use "Integrated Security=SSPI;persist security info=False; Trusted_Connection=Yes."

If a web based app, then you will also need to include <authentication mode = "Windows"/> <identity impersonate="true"/> in your web config file.

Being a small-medium target market, you need to make it "easy" for them to use the app. A lot of them do struggle with intricate and different settings, so, if all they need to do is press "next" while they install, then so much the easier.

If it is all "this side of the firewall" for internal use, then make it easy and use Windows Security - they can control that without having to get inside SQL Server.

Anyway, I think this is now a slightly different question and topic. You could ask in the appropriate developer TA's and get more advice about development and deployment strategies.
0
 

Author Comment

by:KnowledgeWare
Comment Utility
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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...
0
 

Author Comment

by:KnowledgeWare
Comment Utility
Thanks to all!
0
 

Author Closing Comment

by:KnowledgeWare
Comment Utility
Thanks to all...sorry I messed up the scoring!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now