BizTalk SSO configuration and SQL Error 18456, Severity 14, State 16

Question

Hello, I'm having a major issue trying to configure a new install of BizTalk Server 2006 (not R2). The server had Biztalk installed on it before, and it was working fine. I've uninstalled Biztalk, removed the databases and jobs from the SQL server, which is a separate machine, and reinstalled Biztalk. The install was successfull, with no errors during the install, and nothing in the install logs.
I'm configuring the Biztalk server to be the SSO master secret server, along with creating a new Biztalk group and registering the biztalk runtime. The process always errors out on creating the SSO database on the SQL server. In the ConfigLog, there are a couple of warnings that the MSSQLServerOLAPService does not exist, then it shows errors on creating the SSO database. THere are 4 in a row. In order, they are:

Error ConfigHelper] [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error ConfigHelper] SQL error: 08001 Native error code: 17 Error ConfigHelper] c:\depotsetupv2\private\common\configwizard\confighelper\sqlhelper.cpp(1176): FAILED hr = 80004005 Error ConfigHelper] c:\depotsetupv2\private\common\configwizard\confighelper\sqlhelper.cpp(918): FAILED hr = 80004005 It then has similar errors trying to create each of the biztalk databases.

On the SQL server, there are corresponding errors in the SQL Server Logs - 2 for each attempt Login failed for user '[USERNAME]'.[CLIENT: [IP ADDRESS]] Error: 18456, Severity: 14, State: 16

The first error from the SQL logs also shows up as a failure audit in the SQL server's application event log.

The biggest issue I am having with this is that the user I am logged on to the Biztalk server is a local admin on both the Biztalk server and the SQL server, and is in the SQL sysadmin group. The user that I am configuring the Biztalk services to run under is also a local admin on both servers and in the sysadmin group on the SQL server. I've checked the MSDTC settings on both machines and made sure they are set as the Biztalk documentation recommends. SQL Browser is runnning on the SQL machine, and I've verified that network access is allowed using the SQL Surface Area Configuration tool.

Can anyone help me find something that I might have missed?

-John

Re: Igal: Yes, all of the servers and users are on the same domain. I've run accross that posting on SQL protocols in researching this, but I tried to select a count from one of the tables in the default database of the logged in user while connected to another database. I had no problems at all running that query.

Re: Yossi: I'm installing BizTalk on Windows Server 2003 R2 SP1. Yes, I have removed the SSODB (Wouldn't out it past myself to miss something like that though!). I will make sure I am providing the usernames correctly and check out the sources you linked and get back to you.

Answer 1

A few of pointers:

• Check out the two points at the end of the Configuring Enterprise SSO Using the Configuration Manager page on MSDN:

When configuring the SSO Windows accounts using local accounts, you must specify the account name without the computer name.

When using a local SQL Server named instance as data store, you must use LocalMachineName\InstanceName instead of LocalMachineName\InstanceName, PortNumber.

• Check out the relevant installation guide (don't worry about the fact that it relates to R2, they seems to have hidden the 'R1' documentation, but they are the same), and specifically the section around "Windows Groups and Service Accounts"

• also - just to be sure - when you have uninstalled BizTalk and removed the databases - you have removed the SSODB as well, right?! :-)

• The log files are very confusing - especially when deciding which error is the acutal problem - have you tried looking up any other errors you've had? (check out this blog entry, for example)

Answer 2

Thank you both for answering this question. I had everything set up properly... Unfortunately for me, the answer was the standard "Windows" answer - reboot and try again. As soon as I rebooted the SQL server, I was able to configure Biztalk just fine. I am going to set Yossi's answer as accepted, however, since that would be the most relevent for anyone else who may be reading this question. Just rememnber to reboot after all setting changes!!!!!!