BizTalk SSO Configuration and SQL Error 18456, Level 14, State 16
I am having a serious problem configuring a fresh installation of BizTalk Server 2006 (not R2). The server used to have BizTalk and it worked fine. I uninstalled BizTalk, removed the databases and jobs from SQL Server, which is a separate machine, and re-installed BizTalk. The installation was successful, with no errors during installation, and nothing in the installation logs.
I am setting up the BizTalk server to a master SSO secret server along with creating a new BizTalk group and registering the BizTalk runtime. The process always resolves errors when creating the SSO database on the SQL server. ConfigLog has a few warnings that MSSQLServerOLAPService does not exist
and then shows errors while creating SSO database. There are 4 in a row. They are okay:
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
Then he has similar errors trying to create each of the BizTalk databases.
The SQL Server has corresponding errors in the SQL Server logs - 2 for each attempt Login failed for user [USERNAME]. [CLIENT: [IP ADDRESS]] Error: 18456, Severity: 14, Status: 16
The first error from the SQL logs also shows up as a failure audit in the SQL Server Application event log.
The biggest problem I am facing is that the user I have registered with the BizTalk server is a local administrator on both the BizTalk server and the SQL server and is in the sysadmin SQL group. The user I am configuring BizTalk services for is also a local administrator on both servers and in the sysadmin group on the SQL server. I checked the MSDTC options on both machines and made sure they were set as the BizTalk documentation recommends. The SQL browser is running on the SQL machine and I have confirmed that network access is allowed using the SQL surface configuration tool.
Can anyone help me find what I might have missed?
Re: Igal:
Yes, all servers and users are in the same domain. I came across this post on SQL Protocols while researching this question, but I tried to select a count from one of the tables in the default logged in user database when connecting to another database. I didn't have any problems with this request.
Re: Yossi:
I am installing BizTalk on Windows Server 2003 R2 SP1. Yes, I removed SSODB (I wouldn’t miss something like this!). I will be sure to provide the usernames correctly and check the sources you are associated with and come back to you.
source to share
Several pointers:
- Look at the two points at the end of Configuring Enterprise SSO using the Configuration Manager page on MSDN:
When configuring Windows SSO using local accounts, you must specify the account name without the computer name.
When using a local SQL Server named instance as the data store, you must use LocalMachineName \ InstanceName instead of LocalMachineName \ InstanceName, PortNumber.
-
Check out the related installation guide (don't worry about it being R2, they seem to have the "R1" documentation hidden, but they are the same), and in particular the "Windows Groups and Service Accounts" section
-
also - just to be sure - when you uninstalled BizTalk and dropped the databases, you also uninstalled SSODB, right?! :-)
-
The log files are very confusing - especially when determining which error is an acutal issue, have you tried looking for any other errors you had? (see, for example, the blog post )
source to share
I had everything set up correctly. Unfortunately for me the answer was the standard "Windows" answer - reboot and try again. Once I rebooted the SQL server, I was able to set up BizTalk just fine.
I'm going to answer Yossi as accepted, however, as that would be most relevant to anyone else who may be reading this question.
Don't forget to reboot after all settings changes!
source to share