November 28, 2012 at 5:41 am
Good day,
Can someone please help me or direct me.
On the SCCM 2012 requirements page for SQL Server Requirements it says the following:
Windows AuthenticationConfiguration Manager requires Windows authentication to validate connections to the database.
Unfortunately I don't have full access to this server so can't change the permissions but surely this does not mean that Mixed Mode is not allowed, after all Mixed mode allows both Windows Authentication and SQL Server Authentication.
Please can I have some input into this?
Thank you.
November 28, 2012 at 5:45 am
Just because SCCM requires windows authentication doesn't mean that you have to alter your SQL Server authentication settings. You just need to make sure security in the database is set up to only use windows accounts, that's all.
Of course, if SCCM requires its own instance, your problem is solved because when you install the new instance you can mark "windows authentication".
November 28, 2012 at 6:46 am
Let me elaborate a little more.
The SQL Server is purely for SCCM. I am writing reports using SSRS and want to use SQL Authentication, but because of the windows only authentication it is currently setup it clearly doesn't work. Setting up every single user that will use the report, well over 1,000, is just not a viable option. The table sI am querying are in the SCCM database.
Do you know if SCCM allows for mixed mode or does it have to be only Windows authentication. I can find nothing that says only windows authentication.
November 28, 2012 at 6:59 am
Best practise is to configure SCCM DB server in Windows only mode, but you can change it to mixed mode.
If your sys admins dont want to change the authentication mode, the easiest way would be to create an AD group for the users needing access to the reports and add that AD group into SQL and SSRS.
November 28, 2012 at 7:27 am
I fail to understand why that is best practice but that is the MS way in the end so nothing I can do about that.
Thanks, that is a good idea. I will have a look at that now. Didn't think of that.
November 28, 2012 at 7:29 am
It is best practise as its the most secure method of handling authentication due to the way it handles tokens and requires authentication from AD, which is that little bit harder to compromise than SQL authentication where the SQL server handles the authentication.
November 28, 2012 at 10:39 am
Ditto to what Anthony said. Additionally, SQL authentication is usually managed locally or more freely than Windows authentication. This fact sometimes makes it easy for a guy to get sysadmin or dbo access from "his buddy the dba" so he can do X project, when he really doesn't even need that access. Whereas more often people who manage AD aren't as easily accessed / hit up for these types of perms because they usually can't manage the SQL Server due to separation of duties.
In my experience (your mileage may vary), getting on a Windows Group takes a hammer, crowbar, and lots of paper lubricant (fill out the request, in triplicate please, then pass on to the Preventer of Information Services). And getting single windows login access to SQL Server is a pain because DBAs don't like granting individual login access. Whereas a SQL login can be "shared" (even though it shouldn't) between large groups of people and carries within it a degree of anonimity that a windows login doesn't...
And, as you can see, I've been hanging around my security-paranoid dev friend waaaay too long. @=)
November 28, 2012 at 12:25 pm
What everyone is saying is true and good. However, I think your real question is asking whether or not you can have mixed mode set. The answer is yes. SCCM will USE the Windows Authentication. That's what it means. It does not mean that you cannot have mixed mode set.
Jared
CE - Microsoft
November 29, 2012 at 1:39 am
Thank you, that is what I was asking. So mixed mode works, that is all I was after.
I more than understand the crazy freaked out paranoid people and many many possible scenarios where something terrible can or could go wrong, I just wanted to know if it can work, and thank yoy, now I know it can, as I thought. Just wish I could find a posting saying so, one from MS, no disrespect but people talking on a forum won't really go a long way in backing me up in saying yes it can be done and does work.
Anyone have any ideas regarding where I can find a post saying mixed mode works?
November 29, 2012 at 4:56 am
plsh (11/29/2012)
Thank you, that is what I was asking. So mixed mode works, that is all I was after.
Which is what I said in my initial reply. @=)
no disrespect but people talking on a forum won't really go a long way in backing me up in saying yes it can be done and does work.
Anyone have any ideas regarding where I can find a post saying mixed mode works?
So you come to a SQL Server forum and ask a question that you weren't intending to accept any answers for?
Call Microsoft. Talk to them. There's no link we can give you that will make you a believer if you're not going to listen to the experts who hang out here.
EDIT: I just realized how snarky this sounds, which is not what I meant to do. This is just a fact. Call Microsoft. Talking to their tech support is the only way you can get the answer that you want.
November 29, 2012 at 5:57 am
In addition to what Brandie said, I would think that the quote in your first post says it all. It doesn't say that Windows Authentication is the ONLY required authentication... It says that Windows Authentication is required. Mixed mode has Windows Authentication. I think, though, that you are missing the point of the statement mentioned. It is saying that SCCM cannot itsef authenticate to SQL Server using SQL Server creds. SCCM will use Windows Authentication. It really doesn't care about what SQL Server is configured for as long as the user that is running SCCM has the proper permissions on SQL.
Jared
CE - Microsoft
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply