SCCM with a remote SQL instance problems

[TCLIndianaNewb]

I have mucked around with SCCM SP1 (R2) in a lab FOREVER. First
attempt was SQL, SCCM, IIS, etc all on one server. Attempt two is
SCCM and IIS on one server, SQL 2005 on a separate server so SCCM
connects to the remote SQL Server.

I've run into a brick wall of sorts. Here are several errors I get:

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"

and

"Setup failed to install SMS Provider"

The second error "Setup failed..." seems to have a consensus that it's
an SPN issue but I thin I've met the requirements on that although I'm
not totally sure. Here's the site with details on SPN:
http://blogs.technet.com/gborger/archive/2009/03/08/troubleshooting-duplicate-spns-for-sccm-remote-databases.aspx

The first error "Login failed..." makes no sense. When I pop the SCCM
installation disk in and attempt to install it SCCM seems to query my
remote SQL server and the first thing that pops up is the Login failed
error, I'm installing this as a domain admin. Others have this issue
too here: http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/764e658a-8619-4079-9227-4c1516af388a

The kicker is (and others have experienced) is that if I install "SQL
Server Management Studio Express" to visually manage my SQL DB's and I
take these steps (which I also posted to the site afore mentioned)...

I browsed my SQL Server install: Security > Logins > Right click "New
Login" > Search > type in "anonymous" and click "check names" > I had
"Windows Authentication" so I left that checked it pops in the account
"NT AUTHORITY\ANONYMOUS LOGON"

Under "Server Rolls", I added the Anonymous Logon to the Sysadmin roll
which I suspect is NOT a good thing.

My installation then goes off without a hitch!!! If I try to delete
the rolls of "NT AUTHORITY\ANONYMOUS LOGON" from the SQL server
everything falls apart.

I can't seem to find an answer to this and I was going to reference my
initial single server install for more details and comparison but I
can't seem to find out if this is normal behavior? I doubt it's best
security practice to have anonymous logon as a sysadmin roll on a SQL
server but some folks on the net are suggesting that's just the way it
is...darn black hat's! :-)

So - anyone else having these problems or know more about SQL than I?

Thanks for any help.

[Garth]

This is one of the reason why I ALWAYS recommend that SQL be on the same
box as the site server, you will not run into these issues.

Secondly Security Best practices state that SQL should be on the same
server as the site server.
http://technet.microsoft.com/en-ca/library/bb735870.aspx

Have you given the site server computer account Admin access to the SQL
server?
Have you given the site server computer account SA access to SQL?

"TCLIndianaNewb" <rfw...@gmail.com> wrote in message
news:cb497b41-1cb0-413f...@18g2000yqa.googlegroups.com:

--

Garth Jones
Chief Architect

www.Enhansoft.com
Enhancing Your Business

[TCLIndianaNewb]

First - One of the things I find impossible to figure out is why the
installer would use that anonymous account to begin with?

Second - The security guide you sent me a link to said to use Windows
Authentication, can you use an SA account for that, I didn't think you
could do both?

I have given the SCCM Server Computer account local admin access by
putting that computer account in the local admin group on the SQL
server, it still uses that stupid anonymous login junk.

I'm also perplexed because of the fact you can do a clustered SQL
server right? If you do a clustered SQL server wouldn't SQL have to
be broken off from the site server? You'd still have these lame
anonymous logon problems.

It just makes no sense, I'll look over the link you sent some more and
try to make heads or tails of it all.

Thanks for your reply!

[TCLIndianaNewb]

Hey Garth - you know what? It would seem that "anonymous logon"
doesn't actually mean you can logon with anonymous, it looks to be a
function of the Windows Authentication method.

http://support.microsoft.com/kb/839569

That post there says...

"When you add the NT AUTHORITY\ANONYMOUS LOGON login to your instance
of Microsoft SQL Server 2000 or Microsoft SQL Server 2005 so that the
instance of SQL Server accepts anonymous logins through Microsoft
Windows Integrated security and then you try to connect to the
instance of SQL Server as an anonymous user, the connection attempt
may not be successful and you may receive the following error
message:"

So, I wasn't trying to add anonymous functionality to SQL, it just
happened, but if you do try to add it you still get denied because
it's not a standard practice for SQL. It seems that SQL for some
reason uses that but doesn't actually authenticate with it, instead it
uses your Windows Credentials. I'll have to try the anonymous logon
via the command line...I would suspect I'd get an error.

[Sherry Kissinger [MVP]]

I'm with Garth on this--talk to whomever you need to talk to internally, and
install SQL and your ConfigMgr on the same box. You'll avoid years of
headaches (like this one).

Sure it's "supported" to do so. But as you can tell it's certainly not
easy. Do you have a *technical* reason to put SQL offbox? The reason of
"our SQL team says so" is NOT good enough IMO. Point them to the Best
Practices link, and get your sql on-box.

[Garth]

The installer should use both the Local System account and your personal
account that is installing ConfigMgr. The local system account would
look like an anonymous connection.

No, I'm not say use SA, what I say was make sure that the computer
account is in the SA group in SQL.

If you are using SQL cluster then you have to make sure the computer
account is Admin on all Servers in the cluster. That the cluster is
Active/ Passive and not Active / Active.

You will run into far fewer issues if SQL is installed on the site
server box. Even companies with 200k+ client do it.

"TCLIndianaNewb" <rfw...@gmail.com> wrote in message

news:5c5049dd-b94e-45e8...@k26g2000vbp.googlegroups.com:

[Garth]

The anonymous account is any account without access defined. So Did you
grant Sysadmin role to the computer account for your site server? Did
you Grant Sysadmin role to the account installing the Site server?

"TCLIndianaNewb" <rfw...@gmail.com> wrote in message

news:1b502c23-4fe1-42fb...@g1g2000vbr.googlegroups.com:

[TCLIndianaNewb]

Thanks for all the discussion.

I think I granted the computer account of my SCCMSERVER as a local
admin on my SCCMSQLSERVER so on the sccmsqlserver in local admin group
I have my sccmserver$ computer account. Is that what you mean Garth?
I installed the site server with the domain admin account so it does
have sysadmin right on both the site server and on the SQL server, I
used SQL Management Studio to check that, under properties of
"sysadmin" under "rolls" I have...

sa
builtin\administrators
nt authority\system
2 sql accounts, they have LONG strings, sqlusrs and agent users
mydomain\administrator

and...unfortunately "nt authority\anonymous logon" because it won't
work unless i have that account in there.

[Garth]

There is something miss configured as you should not need Anonymous
setup at all. For that matter it will allow anyone to have full access
to the SQL server and that is not a good idea.

Is this a cluster SQL setup?

"TCLIndianaNewb" <rfw...@gmail.com> wrote in message

news:72273e58-212c-4609...@j32g2000yqh.googlegroups.com:

[BGam]

The point of the stuff below is this the WARNING if you get it from the
PreReq check is not a WARNING it is a FAILURE. Make the SQL Server you are
using Remotely use (WINDOWS AUTHENTICATION ONLY)

I was also having the issue with the following setup.

1) All VMware Servers
2) Database server (Windows 2008 x64, SQL Server 2008 SP1 x64 w/4 named
instances and SQL Server 2005 SP3 x64 w/3 named instances - all mixed moded
authenticaion)
4) SCCM Server (Windows 2008 x64, SCCM 32bit)
5) All the pre-reqs were in place. The Administrative rights for the service
account that was going to run SCCM, WebDAV for IIS, SPN(s) for the service
account the run the SQL Server Service on the server, Domain Memberships, AD
Scheme extentions, updates (KB911897, 912818, 913538, 914389, 925903 &
932303), Short file name support, MMC version 3.0, Microsoft .NET, XML Core
Services 6.0, WSUS SDK, WSUS 3.0, Windows Remote Management, Widnows Remote
Differential Compression, MMC and .NET Updates, SQL Server Admin Permissions,
Site Computer Admin permissions, Database Server Local Administrative
permissions, IIS, BITS and WebDAV (stated earlier) are all enabled and passed.
6) For permissions I had the SCCM server as a local Administrator on the
Database server and the AD account I was using to install SCCM as a local
Administrator as well on the Remote Database Server.
7) For the Database permissions I had the AD Account I was installing SCCM
with as a sysadmin and the <domain>\computer$ name as a sysadmin as well on
the database.
8) The Port for the SQL server is Dynamic set and the SQL Browser serivce
keeps track of the instances. To get the Dynamic Port for the SQL instance
you are using open your SQL Configuration Manager and go to your SQL instance
TCP/IP Protocal Properties and scroll all the way to the bottom until you see
Dynamic Port on the Left. That is the port you need for the next step.
9) The SPN(s) were made as the following (##### = Dynamic Port).
MSSQLSvc/SERVER:#####, MSSQLSvc/SERVER.<Domain>.local:#####,
MSSQLSvc/SERVER:<InstanceName>,
MSSQLSvc/SERVER.<Domain>.local:<InstanceName>. (NOTE: The service account
they are refering to is the Service Account that starts up the SQL Service on
the Database server not the Account that is going to be the Service Account
for SCCM).
10) AD Scheme was extended and I created a "System Management" object under
CN=System with the ADSI Edit tool. The properties of the "System Management"
object had the SCCM Server added to the Security Tab with Full Control.
11) Probably missing few items but that is enough detail :)

After many trial and error installs I came to the conclusion that all the
above items were setup correctly. There had to be something else wrong. The
only (WARNING) I got from the PreReq check is that the SQL Server was in
Mixed mode. I had tried everything and it always failed at the SMS Provider.
I could not get the $%@! thing to work. I had tried install it 18 times and
was fed up. I came across the the anonymous authentication issue while trying
to authenticate against SQL. I had read many posts about tracing the logs to
find out was failing to Authenticate. I read everyone elses logs and decided
I did not need to do this because all there logs showed the same results.
Instead of messing around with all that I decided to Install another instance
onto one of our servers this time I used (Windows Authentication Only) not
mixed mode. I had to install the new instance because we had databases that
used both authetication methods on the other instances. This resolved my
issue on the first run. Not one failure or WARNING as Microsoft calls it
during the PreReq check. Microsoft needs to fix this some how it should not
be a WARNING the WARNING made me lose a whole day of work and a co-work about
another 2 days if not more.