Posts Tagged ‘SQL’

Today I’m returning to one of my favorites topics … no not transformations, this time is all about “Errors and Warnings, Causes and Solutions”.

Today I encountered the following issue when I was trying to connect to a remove SQL Server database:

“The local MS DTC detected that the MS DTC on <server name> has the same unique identity as the local MS DTC. This means that the two MS DTC will not be able to communicate with each other. This problem typically occurs if one of the systems were cloned using unsupported cloning tools. MS DTC requires that the systems be cloned using supported cloning tools such as SYSPREP. Running ‘msdtc -uninstall’ and then ‘msdtc -install’ from the command prompt will fix the problem. Note: Running ‘msdtc -uninstall’ will result in the system losing all MS DTC configuration information.”

MSDTC-has-the-same-unique-identity

CAUSE

The error message is quite lucid about the main reason of this error, this normally occurs when the machines are cloned: “systems be cloned using supported cloning tools such as SYSPREP.

SOLUTION

This is one of those very few cases that the error message will give us the actually cause of the problem and the solution: “Running ‘msdtc -uninstall’ and then ‘msdtc -install’ from the command prompt will fix the problem.”

So, to solve this problem you need to:

  • Click Start, and then click Command Prompt.
  • At the command prompt, type “msdtc -uninstall”, and then press ENTER.
  • At the command prompt, type “msdtc -install”, and then press ENTER.

Note: To perform this procedure, you must have membership in Administrators, or you must have been delegated the appropriate authority.

However, after I resolve this condition by uninstalling and then reinstalling the Microsoft Distributed Transaction Coordinator service (MSDTC), when I access the BizTalk Administration Console and try to refresh (F5) the Group Hub Page I got this additional error:

“The Microsoft Distributed Transaction Coordinator (MSDTC) may not be configured correctly. Ensure that the MSDTC service is running and DTC network access is allowed on the BizTalk, SQL and SSO Master servers. For more information, see "MSDTC Configuration settings required for BizTalk Server" in the BizTalk Server Help.”

MSDTC-may-not-be-configured-correctly

I try to restart the MSDTC service or other services like the Enterprise Single Sign-On Service without any success… to avoid spending more time around this issue, and because this was a DEV standalone machine, I decided to restart the server… and that did the trick, after the restart everything was working without any problem.

This next posts will focus on installing the BizTalk prerequisites and perform the necessary configuration on BizTalk Server machine.

Before installing BizTalk Server or its prerequisites, make sure you have installed the latest critical Windows updates from Microsoft.

Install SQL Server 2014

BizTalk Server provides the capability to specify a business process and also a mechanism by which the applications used in that business process can communicate with each other. SQL Server is the main repository for this communication mechanism. For optimal performance, Microsoft recommends using the Enterprise Edition of SQL Server.

Note: Using SQL Server Express Edition is not recommended or supported. The Express edition does not include certain features needed by BizTalk Server.

Note: BizTalk Server supports SQL Standard Edition version. However to use Business Activity Monitoring real-time aggregation (BAM RTA), you must install SQL Server Enterprise Edition because BAM real-time aggregation (RTA) is not supported in the Standard Edition of SQL Server.

Note: To fully use the BizTalk Server SDK or deploy BizTalk Server applications from a Visual Studio, you should install the SQL Server Development Tools

Note: BizTalk Server supports all case-sensitive and case-insensitive SQL Server collations except for binary collations. Binary collations are not supported.

To install SQL Server 2014:

  • Insert the SQL Server installation disk into the DVD-ROM drive. The setup program will begin automatically. If prompted for automatic installation of prerequisite software, click “OK”
  • On the SQL Server Installation Center, click “Installation” and then click “New SQL Server stand-alone or add features to an existing installation”

23-bts-2013-r2-sql-server-2014-installation-center-New-SQL Server-stand-alone-add-features-existing-installation

  • On the Product Key page, enter your product key and click “Next”

23-bts-2013-r2-sql-server-2014-product-key

  • On the License Terms page, select “I accept the license terms”, and then click “Next”

24-bts-2013-r2-sql-server-2014-license-terms

  • In the Global Rules page, the setup procedure will automatically advance to the Product Updates window if there are no rule errors. Otherwise review the information and resolve any issues, and then click “Next” to continue.

25-bts-2013-r2-sql-server-2014-global-rules

  • On the Product Updates page, the latest available SQL Server product updates are displayed. If no product updates are discovered, SQL Server Setup does not display this page and auto advances to the Install Setup Files page. Otherwise review the information and resolve any issues, and then click “Next” to continue.

26-bts-2013-r2-sql-server-2014-Product-Updates

  • On the Install Setup files page, Setup provides the progress of downloading, extracting, and installing the Setup files. If an update for SQL Server Setup is found, and is specified to be included, that update will also be installed.
    • The System Configuration Checker verifies the system state of your computer before Setup continues.
    • If prompted to restart the computer, click “OK”
  • On the Install Rules page, another rule check will be run to ensure everything is in place so the setup will be successful. If a rule check fails, the setup application will provide the corrective measures to take so installation may proceed. Click on “Next” to continue.

26-bts-2013-r2-sql-server-2014-Install-Rules

  • On the Setup Role page, select “SQL Server Feature Installation”, and then click “Next” to continue to the Feature Selection page.

27-bts-2013-r2-sql-server-2014-setup-role

  • On the Feature Selection page, select the following features, and then click “Next”
    • Database Engine Services
      • SQL Server Replication
      • Full-Text and Semantic Extractions for Search
    • Analysis Services
    • Reporting Services – Native
    • Shared Features
      • SQL Server Data Tools
      • Client Tools Connectivity
      • Integration Services
      • Management Tools – Basic
        • Management Tools – Complete

29-bts-2013-r2-sql-server-2014-feature-selection-1

29-bts-2013-r2-sql-server-2014-feature-selection

  • On the Feature Rules page, Setup verifies the system state of your computer before Setup continues. The Feature Rules page will automatically advance if all rules pass. Otherwise review the information and resolve any issues, and then click “Next” to continue.

30-bts-2013-r2-sql-server-2014-Feature-Rules

  • On the Instance Configuration page, select “Default instance”, leave the rest of the default values, and then click “Next” to continue.

31-bts-2013-r2-sql-server-2014-instance-configuration

  • On the Server Configuration page, you can specify login accounts for each SQL Server services. You can assign the same login account to all SQL Server services, or you can configure each service account individually. You can also specify whether services start automatically, are started manually, or are disabled (Microsoft recommends that you configure service accounts individually to provide least privileges for each service).
    • Leave the default login account to all SQL Server services
    • Configure all of them with the startup type: “Automatic”
    • And then click “Next”

32-bts-2013-r2-sql-server-2014-server-configuration

  • On the Database Engine Configuration page, select “Windows authentication mode”, click “Add Current User”, and then click “Next”
    • Authentication Mode: Select “Windows authentication mode”. If you select Mixed Mode Authentication, you must provide a strong password for the built-in SQL Server system administrator account.
    • Specify SQL Server administrators: You must specify at least one system administrator for the instance of SQL Server. To add the account under which SQL Server Setup is running, click “Add Current User”

33-bts-2013-r2-sql-server-2014-database-engine-configuration

  • On the Analysis Services Configuration page, select “Multidimensional and Data mining Mode”, click “Add Current User”, and then click “Next”
    • Server Mode: Select “Multidimensional and Data mining Mode”. Server mode determines which memory and storage subsystems are used on the server. If you plan to run multidimensional cube databases on the server, choose the default option, Multidimensional and Data Mining server mode.
    • Specify which users have administrative permissions for Analysis Services: you must specify at least one system administrator for Analysis Services. To add the account under which SQL Server Setup is running, click Add Current User.

34-bts-2013-r2-sql-server-2014-analysis-services-configuration

  • On the Reporting Services Configuration page, select “Install and configure”, and then click “Next” to continue.

35-bts-2013-r2-sql-server-2014-reporting-services-configuration

  • On the Feature Configuration Rules page, this is another steep to checks if everything is fine, setup will automatically advance to the next page if all rules pass. Otherwise review the information and resolve any issues, and then click “Next”

36-bts-2013-r2-sql-server-2014-Feature-Configuration-Rules

  • On the Ready to Install page, review the information, and then click “Install”

37-bts-2013-r2-sql-server-2014-Ready-to-Install

  • On the Complete page, click “Close”

39-bts-2013-r2-sql-server-2014-complete

Note: Once again, after this installation, I like to do a Windows update, this step is not necessary.

Related links

BizTalk is shipped out with a total of 13 SQL Agent jobs. Experienced BizTalk professionals know that all the BizTalk SQL Server Agent jobs except the MessageBox_Message_Cleanup_BizTalkMsgBoxDb job should be enabled and running successfully. Besides this job the other jobs should not be disabled!

However one of the most common and important tools to resolve problems that happens in BizTalk Server is the BizTalk Terminator tool (you can know more about this tool here). Terminator provides the easiest way to resolve most of these issues identified by the Monitor BizTalk Server Job.

Nevertheless, before running the Terminator tool, you must always make sure that:

  • you have a BizTalk Backup of your databases
  • all the BizTalk hosts instances have been stopped,
  • and all BizTalk SQL Agent jobs have been disabled.

Unfortunately, through SQL Server Management Studio console there is no easy way to disable or enable all jobs, forcing us to go one on one to disable or enable them.

disable-BizTalk-SQL-Server-Agent-jobs

This query will disable all the BizTalk SQL Server Agent jobs (including the MessageBox_Message_Cleanup_BizTalkMsgBoxDb):

update msdb.dbo.sysjobs set [enabled] = 0 where [name] = 'Backup BizTalk Server (BizTalkMgmtDb)'
update msdb.dbo.sysjobs set [enabled] = 0 where [name] = 'CleanupBTFExpiredEntriesJob_BizTalkMgmtDb'
update msdb.dbo.sysjobs set [enabled] = 0 where [name] = 'DTA Purge and Archive (BizTalkDTADb)'
...
update msdb.dbo.sysjobs set [enabled] = 0 where [name] = 'TrackedMessages_Copy_BizTalkMsgBoxDb'
update msdb.dbo.sysjobs set [enabled] = 0 where [name] = 'MessageBox_Message_Cleanup_BizTalkMsgBoxDb'

After all the task are complete we need of course to enable and start everything. This query will enable all the BizTalk SQL Server Agent jobs (with the exception of MessageBox_Message_Cleanup_BizTalkMsgBoxDb):

update msdb.dbo.sysjobs set [enabled] = 1 where [name] = 'Backup BizTalk Server (BizTalkMgmtDb)'
update msdb.dbo.sysjobs set [enabled] = 1 where [name] = 'CleanupBTFExpiredEntriesJob_BizTalkMgmtDb'
update msdb.dbo.sysjobs set [enabled] = 1 where [name] = 'DTA Purge and Archive (BizTalkDTADb)'
...
update msdb.dbo.sysjobs set [enabled] = 1 where [name] = 'TrackedMessages_Copy_BizTalkMsgBoxDb'

The SQL queries can be found and download on Microsoft TechNet Gallery:
SQL Query to Disable All BizTalk SQL Server Agent jobs
Microsoft TechNet Gallery

 

SQL Query to Enable All BizTalk SQL Server Agent jobs
Microsoft TechNet Gallery

Almost three weeks ago in my last post I release a SQL script to manager and delete the unnecessary records in the BizTalk “MarkLog” tables according to some of the best practices (you can read more here: Managing and cleaning BizTalk Server MarkLog database tables according to some Best Practices), however I knew in advance that the script had some limitations:

  • Basically the user needs to update the script manually after “installing” it. He/she needs to add and remove the databases that might not be in use (like BAM).
  • Or add, if more than one MsgBox is used.

But the beautiful of the BizTalk Community is that sometimes they provided awesome feedback, and that was exactly what happened in this case. 10 days after I published my post, I received an email from Mikael Sand, Mikael is a strong BizTalk Community member from Sweden that I had the pleasure of meeting him in person, I think the first time was two years ago in Norway, describing this exact limitations but… you are making a mistake if you think that he only described the limitations!!

Indeed he spent some time describing the limitations but I also provided me with an updated scripts with all the improvement necessary to suppress these limitations! Basically Mikael updated the script using an identical logic from another SP in the backup job so that user doesn’t need to update the script once it is installed. Also, it does not need to be updated if a database (like BAM) is added later or if you are using more than one MsgBox.

Basically the differences between the first release and this one is that:

  • Instead of hard coding implement all the delete necessary queries
/****** Delete MarkLog History from BAMAlertsApplication database ******/
DELETE FROM [BAMAlertsApplication].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BAMArchive database* *****/
DELETE FROM [BAMArchive].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BAMPrimaryImport database ******/
DELETE FROM [BAMPrimaryImport].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkDTADb database ******/
DELETE FROM [BizTalkDTADb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkMgmtDb database ******/
DELETE FROM [BizTalkMgmtDb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkMsgBoxDb database ******/
DELETE FROM [BizTalkMsgBoxDb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkRuleEngineDb database ******/
DELETE FROM [BizTalkRuleEngineDb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from SSODB database ******/
DELETE FROM [SSODB].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep
  • Mikael have implementing this same operation dynamically by “stolen” the cursors logic and realservername from the BizTalk procedure “sp_MarkBTSLogs”. The cursor iterates all the databases that are backed up by BizTalk.
DECLARE @BackupServer sysname, @BackupDB sysname, @RealServerName sysname
DECLARE @tsql nvarchar(1024)
DECLARE @ret int
/* Create a cursor */
DECLARE BackupDB_Cursor insensitive cursor for
   SELECT ServerName, DatabaseName
   FROM	admv_BackupDatabases
   ORDER BY ServerName

open BackupDB_Cursor
fetch next from BackupDB_Cursor into @BackupServer, @BackupDB
WHILE (@@FETCH_STATUS = 0)
   BEGIN
      -- Get the proper server name
      EXEC @ret = sp_GetRemoteServerName @ServerName = @BackupServer, @DatabaseName = @BackupDB, @RemoteServerName = @RealServerName OUTPUT

      /* Create the delete statement */
      select @tsql =
      'DELETE FROM [' + @RealServerName + '].[' + @BackupDB + '].[dbo].[MarkLog]
      WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),''_'',''''), GETDATE()) > ' + cast(@DaysToKeep as nvarchar(5) )
                            
      /* Execute the delete statement */
      exec (@tsql)
      /* Get the next DB. */
      fetch next from BackupDB_Cursor into @BackupServer, @BackupDB
   END
close BackupDB_Cursor
deallocate BackupDB_Cursor

Then I spent some days testing and improving a little more the script, and again by looking to the same SP: “sp_MarkBTSLogs”, I was able to implemented some kind of error handling, just in case of something fails that an exception will be raised, as you can see in this example picture bellow:

sp_DeleteBackupHistoryAndMarkLogsHistory-error-handling

Special thanks for Mikael Sand for the feedback and for this new implementation logic, and of course to the previous persons involved: Tord Glad Nordahl, Rui Romano and Pedro Sousa.

You can download the last version of the custom sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure from:

BizTalk Server: Cleaning MarkLog Tables According to Some of the Best Practices (1.2 KB)
Microsoft | TechNet Gallery

All the BizTalk database which is being backed up by the ‘Backup BizTalk Server’ job, so all databases with the exception of the BAM Star Schema database (BAMStarSchema), has one table called “MarkLog”.

These tables are holding all the transaction marks (they are actually timestamps in a string format), set to a specific database, created by the 3th step (MarkAndBackUpLog) of the ‘Backup BizTalk Server’ job. This step, MarkAndBackupLog, is responsible for marking the logs for backup, and then backing them up. So each time this step runs, by default each 15 minutes, a string is stored on that table with the following naming convention:

  • Log_<yyyy>_<MM>_<dd>_<HH>_<mm>_<ss>_<fff>

Where:

  • Log”: Constant string
  • yyyy: The year as a four-digit number.
  • MM: The month, from 01 through 12
  • dd: The day of the month, from 01 through 31.
  • HH: The hour, using a 24-hour clock from 00 to 23.
  • mm: The minute, from 00 through 59.
  • ss: The second, from 00 through 59.
  • fff: The milliseconds in a date and time value.

Again this marks are timestamps in which the Log backups were set to be made. Example:

  • BTS_2014_05_06_14_15_01_327

Unfortunately BizTalk has no out-of-the-box possibilities to clean up these tables. And the normal procedure is to run the terminator tool to clean it up. See also: Clean up the MarkLog table with Terminator

Terminator-Tool-Purge-MarkLogs

BizTalk Terminator is an excellent tool that allows for common BizTalk problems to be addressed by scripts provided by the BizTalk Team, but needs to be used carefully and by users who know what they are doing.

Although most of the times this is extremely useful tool and our friend, in this particular operation/situation using this tool it’s not really the best option for two reasons:

  • Using this tool means that we need to stop our environment, i.e., downtime in our integration platform.
  • And if we look at the description of this “PURGE Marklog table” task it says that this operation calls a SQL script that cleans up everything in Marklog table – and maybe this is not a best practices!

I spoke to my dear friend, and one of the best BizTalk Administrator that I know, Tord Glad Nordahl to discuss and know what should be the best practice to maintain these tables:

  • Is these information (timestamps) useful for BizTalk Administrators?
  • Should I clean all the data inside this tables or should I maintain a history?
  • Is safe to clean this information in runtime?

Were some of the questions that we discussed.

Is these information (timestamps) useful for BizTalk Administrators? Should I clean all the data inside this tables or should I maintain a history?

For the same reason that we maintain a Backup history in the Adm_BackupHistory table controlled by the step “Clear Backup History” of the ‘Backup BizTalk Server’ job. This information is important for example to keep an eye on the backup/log shipping history records to see whether the back is working correctly and data/logs are restored correctly in the stand by environment. The information on the MarkLog tables are also useful for the BizTalk Administration team!

So as long as the MarkLog tables have the same info (data from the same dates) as the backup job days to keep you can safely delete the rest of the information.

As a best practices: you should respect the @DaysToKeep parameter that you specify in the “Clear Backup History” step of the ‘Backup BizTalk Server’ job.

And this is why that in my opinion, you shouldn’t use the Terminator tool to perform this operation!

Is safe to clean this information in runtime?

The rows in the Marklog table are not “required” and can be cleaned whenever you want as long the BizTalk Backup Job is not running.

Cleaning MarkLog Tables According to Some of the Best Practices

So the main challenger is how can we safely delete and maintain a history of all MarkLog tables according to some of the best practices described earlier?

My first approach was creating a new job that according to a scheduler would run a stored procedure to delete all the unnecessary information on that tables but I realized I could have two problems with this approach:

  • I need to be sure that BizTalk Backup Job wasn’t running performing the backups;
  • And I didn’t want to have to places to define the @DaysToKeep and I didn’t want to make unnecessary joins or additional selects

I was trying to find an approach for this operation to be carried out in an easy way and as fast as possible.

So I end up recreating sp_DeleteBackupHistory (that is configured is the Backup BizTalk Server (BizTalkMgmtDb) job in the last step) with a different name sp_DeleteBackupHistoryAndMarkLogsHistory and configure the job to run this step:

CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndMarkLogsHistory] @DaysToKeep smallint = null, @UseLocalTime bit = 0
AS
 BEGIN
	set nocount on
	IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
		RETURN
	/*
		Only delete full sets
		If a set spans a day such that some items fall into the deleted group and the other don't don't delete the set

		Delete history only if history of full Backup exists at a later point of time
		why: history of full backup is used in sp_BackupAllFull_Schedule to check if full backup of databases is required or not.
		If history of full backup is not present, job will take a full backup irrespective of other options (frequency, Backup hour)
	*/

	declare @PurgeDateTime datetime
	if (@UseLocalTime = 0)
		set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETUTCDATE())
	else
		set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETDATE())

	DELETE [dbo].[adm_BackupHistory]
	FROM [dbo].[adm_BackupHistory] [h1]
	WHERE 	[BackupDateTime] < @PurgeDateTime
	AND	[BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [h1].[BackupSetId] AND [h2].[BackupDateTime] >= @PurgeDateTime)
	AND EXISTS( SELECT TOP 1 1 FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] > [h1].[BackupSetId] AND [h2].[BackupType] = 'db')

	/****** Delete MarkLog History from BAMAlertsApplication database ******/
	DELETE FROM [BAMAlertsApplication].[dbo].[MarkLog]
    WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BAMArchive database* *****/
	DELETE FROM [BAMArchive].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BAMPrimaryImport database ******/
	DELETE FROM [BAMPrimaryImport].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkDTADb database ******/
	DELETE FROM [BizTalkDTADb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkMgmtDb database ******/
	DELETE FROM [BizTalkMgmtDb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkMsgBoxDb database ******/
	DELETE FROM [BizTalkMsgBoxDb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkRuleEngineDb database ******/
	DELETE FROM [BizTalkRuleEngineDb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from SSODB database ******/
	DELETE FROM [SSODB].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep
 END

Steps required to install/configure

  • Create the sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure described earlier
  • You need to adjust the SQL Script to your environment because some environment may not have all features installed, for example: Business Rules and in this case BizTalkRuleEngineDb database will not exist.
  • Change and configure the 4th step of the ‘Backup BizTalk Server’ job – “Clear Backup History” to call this new stored procedure

sp_DeleteBackupHistoryAndMarkLogsHistory

Note: Do not change or delete the “sp_DeleteBackupHistory”!

Special thanks for Tord Glad Nordahl for reviewing and all the feedback, and to my friends at DevScope Rui Romano and Pedro Sousa for the helping me developing this SQL Query.

Hope you find it useful

You can download the custom sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure from:

BizTalk Server: Cleaning MarkLog Tables According to Some of the Best Practices (1.2 KB)
Microsoft | TechNet Gallery

 

BizTalk Server databases and their health are very important for a successful BizTalk Server database messaging environment. BizTalk is shipped out with a total of 13 SQL Agent jobs that perform important functions to keep your servers operational and healthy.

Like any other system, all BizTalk Server databases should be backed up and BizTalk Server will provide out-of-the-box a job for accomplished that: Backup BizTalk Server (BizTalkMgmtDb) job.

This job makes both Full and Log backups. By default the Backup BizTalk Server job performs a full backup once a day and performs log backups every 15 minutes. This means that once the full backup is performed you need to wait 24 hours for it to automatically do another full backup of the BizTalk Server databases… even if you try to manually run the job, it will only make the backups of the log files.

But sometimes we need, for several reasons, to have the ability and the possibility to force a full backup:

  • We will have some maintaining plan on the server, or apply a new configuration, and we want to backup the environment
  • Or simple we will install a new integration application and again we want to have a backup in this exact moment

Each company have is policies, so again for several reasons, we sometimes need to force a full backup of all BizTalk Server databases.

The standard way is to use the “BizTalkMgmtDb.dbo.sp_ForceFullBackup” stored procedure. However, and unlike what many people think, this stored procedure does not perform a full backup it only marks internally on BizTalk Server databases that the next time the Backup BizTalk Server job runs it will need to perform a full backup of the data and log files.

All BizTalk Admins know this (if they don’t know, they should!), however sometimes we need to really on DBA Admins to perform this task, and do not misunderstand me, I have nothing against DBA! All the BizTalk Administration team should be composed by several element with different skills: DBA Admins, network admins, Sys Admins and a BizTalk Admins… each playing its role. But sometime DBA don’t really understand the BizTalk infrastructure and BizTalk Administrator should have an important role advising and helping them (like in other situations it will be the opposite).

And because this is not a day by day task people tend to forget all the steps and sometimes they will create custom scripts to perform this task, but you really need to be aware of two important things:

  • The Backup BizTalk Server job is the only supported method for backing up the BizTalk Server databases. Use of SQL Server jobs to back up the BizTalk Server databases in a production environment is not supported.
  • You can use the SQL Server methods to backup the BizTalk Server databases only if the SQL Server service is stopped and if all BizTalk Server processes are stopped.

So to help one of my DBA teams I end up creating this job that is composed by two steps:

  • Step 1: Force Full Backup
    • That will call the “BizTalkMgmtDb.dbo.sp_ForceFullBackup” stored procedure
  • Step 2: Backup BizTalk Server
    • That will call the standard Backup BizTalk Server (BizTalkMgmtDb) job

Force-Full-Backup-BizTalk-Server-BizTalkMgmtDb

Note: Of course this custom job should be disabled and manually executed when you need it!

Hope you find it useful

You can download the custom “Force Full Backup BizTalk Server (BizTalkMgmtDb)” Job from:

Force Full Backup BizTalk Server (BizTalkMgmtDb) Job (863 bytes)
Microsoft | TechNet Gallery

 

BizTalk Server database databases and their health are very important for a successful BizTalk Server database messaging environment. This is nothing new and everybody knows!

Although there can be many settings that we can configure, like auto-growth settings for BizTalk Databases (you can learn more here), there are two main things that we must understand and be aware, especially the database administrators:

  • Execution of the BizTalk Server SQL Agent jobs are crucial for managing the BizTalk Server databases and for maintaining optimal performance.
  • The Backup BizTalk Server job is the only supported method to backup the BizTalk Server databases and requires that all of the BizTalk Server databases are configured to use the SQL Server full recovery model.

BizTalk Server 2010/2013 is shipped out with a total of 13 SQL Agent jobs. 2 of these jobs must be configured. The two jobs that needs configuration are the two most important jobs:

  • The “Backup BizTalk Server“: This is the job provided by Microsoft to do a best practice backup of the BizTalk databases.
  • And the “DTA Purge and Archive“: This SQL Agent job purges and archives information from the tracking database, you do need to configure this job in order for it to work.

However what many times we forget is that this two jobs, by default, don’t provide functionalities for deleting backup files that have accumulated over time on our file system and we normally forget to create a “process” or a “job” to accomplish this until is too late. The result of that is… lots of times we just remember when disks are full and everything stop to work!

We can for example implementing your custom “sp_ DeleteBackupHistoryAndFiles” as you can see in my post: BizTalk 2013 Installation and Configuration – Configure BizTalk Server SQL Jobs (Part 15), however I personally don’t like this approach for two reasons:

  • I don’t like to change the scripts of the standard BizTalk jobs
  • And I also believe that this approach is very limited and doesn’t allow the flexibility that we all want.

Instead I prefer to create an SQL Server Maintenance Plan to delete BizTalk Database Backups to accomplish this task.

Maintenance Plan to clean BizTalk Database backup’s files

Maintenance Plans allows DBA’s to have flexibility to create a workflow to execute several tasks required to make sure that database are optimized, regularly backed up, and free of inconsistencies. However almost all of these tasks are warranted by the existing BizTalk jobs and I will not use them. The only thing we need is to create a task to clean BizTalk Database backup’s files from our file system.

The main advantage of this approach is that will allow us more flexibility for further changes and we can also use them to other backup’s that we have.

How to create a Maintenance Plan to clean BizTalk Database backup’s files

As a personal note, you should ensure that SQL Server Agent service is running because the maintenance plans depend on the Microsoft SQL Server Agent in order to run on a regular basis.

Important Note: this example is created to run in a developer machine, so if you are implementing this approach in production environments make sure to keep the backups for the time you see that fit your requirements or make sure the backups are save in an external storage before you delete this files from the file system.

To create the maintenance plan you need to:

  • Open the SQL Server Management Studio by choosing Start > Programs > Microsoft SQL Server [edition] > SQL Server Management Studio.

Or

  • Press the “Windows key” to switch to Windows Server 2012 Start Screen and type “SQL Management” or “SQL” and click in “SQL Server Management Studio” option on Apps menu.
  • Expand the server and then the “Management” folder.
  • Right-click “Maintenance Plans” and select “Maintenance Plan Wizard”. This launches the wizard and you can now step through and create a plan customized to meet your maintenance requirements.

create-new-Maintenance-Plan-Wizard

  • On the SQL Server Maintenance Plan Wizard page, click “Next”.

SQL-Server-Maintenance-Plan-Wizard-page

  • On the “Select Plan Properties” page:
    • In the “Name”: enter the name of the maintenance plan you are creating.
    • In the “Description”: enter a briefly describe your maintenance plan.
    • In the “Run as”: specify the credential that Microsoft SQL Server Agent uses when executing the maintenance plan – leave the default.
    • Select “Single schedule for the entire plan or no schedule” to specify the recurring schedule of the maintenance plan.

Select-Plan-Properties-page

    • Under Schedule, click “Change…”
      • Under “Frequency”, on the “Occurs” list, select “Daily” and in the “Recurs every” box, enter how often the job schedule repeats in days: 1 Day.
      • Under “Daily frequency”, select “Occurs once at” and specific the time of day when the job schedule should run: 12:00:00
      • Leave the default values in the rest of the properties and click “OK”

New-Job-Schedule

  • Back to the Select Plan Properties page, click “Next”
  • On the “Select Maintenance Tasks” page, select “Maintenance Cleanup Task” from the list and click “Next”

Select-Maintenance-Tasks

  • On the “Select Maintenance Task Order” page, click “Next” to continue
  • On the “Define Maintenance Cleanup Task” page, specify the following properties:
    • Under “Delete files of the following type”: select “Backup files”
    • Select “Search folder and delete files based on an extension” to delete all files with the specified extension in the specified folder
      • Under “Folder”: specify the path and name of the folder containing the files to be deleted.
      • Under “File extension”: Provide the file extension of the files to be deleted.
      • Select also “Include first-level subfolders” option if to want to delete the files also from first-level subfolders under the folder specified in Folder.
    • Select “Delete files based on the age of the file at task run time” and specify the minimum age of the files that you want to delete under “Delete files older than the following” property
      • Specify 1 Day
    • Click “Next”

Define-Maintenance-Cleanup-Task

  • On the “Select Report Options“ page, click “Next”.
  • On the “Complete the Wizard” page, verify the choices made on the previous pages, and click Finish.

Complete-the-Wizard

  • On the Maintenance Wizard Progress page, verify if every action where successful executed and then click “Close”

Maintenance-Wizard-Progress

Today is a special day for me, it’s my birthday, and I will continue the tradition of giving something to the community on this day (I started this tradition last year)… In the past I wrote a series of posts describing step by step how to install and configure BizTalk Server 2010.

One of this step is to configure SQL Server Network Protocols, in special ensure that TCP/IP is enable and Shared Memory is disable. You can see how to accomplish this using SQL Server Configuration Manager tool here.

However a community member (anonymous) left me a comment asking me whether it was possible to accomplish this (disable shared memory) from command line or registry and the answer is: YES, of course, you can for example configure all the SQL Server Network Protocols with PowerShell.

All network protocols are installed by SQL Server Setup, but may or may not be enabled. And you need to be aware that this protocols can have impact in your BizTalk Environment, for example:

  • Under certain stress conditions (such as clients accessing SQL Server from the same computer), the SQL Server Shared Memory protocol may lower BizTalk Server performance.
  • BizTalk Server loses connectivity with a remote SQL Server computer that houses the BizTalk Server databases and this may happen if the necessary protocols for SQL Server are not enabled.

So normally we need to perform the following configuration:

  • Disable the “Shared Memory” and “VIA” protocols
  • And Enable the “TCP/IP” and “Named Pipes” protocols
How can I configure SQL Server Network Protocols with PowerShell?

This is a simple script to configure SQL Server Network Protocols for SQL Server that houses BizTalk Server databases:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

##################################################################
# Function to Enable or Disable a SQL Server Network Protocol
##################################################################
function ChangeSQLProtocolStatus($server,$instance,$protocol,$enable){

	$smo = 'Microsoft.SqlServer.Management.Smo.'

	$wmi = new-object ($smo + 'Wmi.ManagedComputer')

	$singleWmi = $wmi | where {$_.Name -eq $server}

	$uri = "ManagedComputer[@Name='$server']/ServerInstance[@Name='$instance']/ServerProtocol[@Name='$protocol']"

	$protocol = $singleWmi.GetSmoObject($uri)

	$protocol.IsEnabled = $enable

	$protocol.Alter()

	$protocol
}

##################################################################
# Enable TCP/IP SQL Server Network Protocol
##################################################################
ChangeSQLProtocolStatus -server "BTS2010LAB01" -instance "MSSQLSERVER" -protocol "TCP" -enable $true

##################################################################
# Enable Named Pipes SQL Server Network Protocol
##################################################################
ChangeSQLProtocolStatus -server "BTS2010LAB01" -instance "MSSQLSERVER" -protocol "NP" -enable $true

##################################################################
# Disable Shared Memory SQL Server Network Protocol
##################################################################
ChangeSQLProtocolStatus -server "BTS2010LAB01" -instance "MSSQLSERVER" -protocol "SM" -enable $false

##################################################################
# Disable VIA SQL Server Network Protocol
##################################################################
ChangeSQLProtocolStatus -server "BTS2010LAB01" -instance "MSSQLSERVER" -protocol "VIA" -enable $false

Because after we correctly set up the protocols, we need to restart the SQL services for the changes to take effect, this script additional also restart all the services and also BizTalk Services if they exist! However this last part is optional.

$service = get-service "MSSQLSERVER"
restart-service $service.name -force #Restart SQL Services

$service = get-service "ENTSSO" #Start Enterprise Single Sign-On Service
if( $service -ne $null )
{
	start-service $service.name
}
get-service BTS* | foreach-object -process {start-service $_.Name} # Start BizTalk Services

Once again I also like to thank my friend Rui Machado for always want and help in all my small challenges, In this particular sample Rui was the creator of the function ChangeSQLProtocolStatus 😉

The script can be found and download on Microsoft TechNet Gallery:
How to set SQL Server Network Protocols in the SQL Server for BTS Databases (7.4 KB)
Microsoft TechNet Gallery

I decided to put the name of the error in the post title … but this post also could be called “Why you shouldn’t delete Active Directory accounts (if you don’t know where it is being used!)”… but before I tell you why, let me try to explain the problem and put some context on it.

Last week I had a funny surprised when I tried to access my BAM Portal in my BizTalk Server 2006 environment

An unspecified error has occured.
Use the navigation bar on the left to access Business Activity Monitoring views.
If the problem persist, contact you System Administrator.

BAM-portal-error

I just love this type of errors! Because I’m also one of the System Administrator, meaning that I was f*$#%& Sorriso.

My first reaction is that it could have been some connectivity problem, and as the error indicates, I tried to navigate the remaining views to see if the problem remained… and surprisingly this problem occurred only in certain views!

BAM-portal

PROBLEM

So at this point I knew it was happening some problem. Of course the first thing we should do is to check the Event Viewer to see if we can find more details about the error and I found three errors related between themselves and associated with BAM:

BAM-Portal-Errors-Event-Viewer

Starting from below:

  • The first error was:

Current User: DOMAIN\sandro
EXCEPTION:
Microsoft.BizTalk.Bam.Management.BamManagerException: Failed to list permissions for BAM view. —> System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
at System.Data.SqlClient.SqlBuffer.get_String()
at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
at Microsoft.BizTalk.Bam.Management.SecurityModule.ListViewPermissions(String viewName, String& dboUsername)
— End of inner exception stack trace —
at Microsoft.BizTalk.Bam.Management.SecurityModule.ListViewPermissions(String viewName, String& dboUsername)
at Microsoft.BizTalk.Bam.WebServices.SecurityHelper.VerifyViewPermissions(String viewName, IPrincipal user, BamManager bamManager, Boolean throwIfNoPermissions)
at Microsoft.BizTalk.Bam.WebServices.SecurityHelper.VerifyViewPermissions(String viewName, IPrincipal user, BamManager bamManager)
at Microsoft.BizTalk.Bam.WebServices.Management.BamManagementService.GetViewDetailsAsXml(String viewName)

Observation: Impossible, I’m BizTalk Administrator but most important my user is the owner of this views!!!

  • The second error was:

Current User: DOMAIN\sandro
EXCEPTION: System.Web.Services.Protocols.SoapException: Internal Server Error.

Observation: says absolutely nothing … trash!

  • And finally the third error was:

(BAMPortal.PortalApplication) Void LogAllErrors(System.Exception[]): System.Web.HttpException: Error executing child request for /BAM/Pages/Search.aspx. —> System.Web.HttpUnhandledException: Exception of type ‘System.Web.HttpUnhandledException’ was thrown. —> System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Internal Server Error.
at Microsoft.BizTalk.Bam.WebServices.Management.BamManagementService.GetViewDetailsAsXml(String viewName)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.BizTalk.Bam.WebServices.ManagementService.BamManagementService.GetViewDetailsAsXml(String viewName)
at Microsoft.BizTalk.Bam.Portal.DataAccess.BamDefinitionCache.FetchViewDefinition(String viewName)
at Microsoft.BizTalk.Bam.Portal.DataAccess.BamDefinitionCache.GetBamDefinition(String viewName)
at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.BuildColumnsCollection()
at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.EnsureColumnsCollection()
at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.ColumnsOfType(ColumnTypes type)
at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.EnsureInstanceColumns()
at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.get_InstanceColumns()
at BAMPortal.ColumnsChooser_ascx.GetColumns()
at BAMPortal.ColumnsChooser_ascx.GetAvailableColumns()
at BAMPortal.ColumnsChooser_ascx.ReconcileColumns()
at BAMPortal.ColumnsChooser_ascx.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
— End of inner exception stack trace —
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.pages_search_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpServerUtility.ExecuteInternal(IHttpHandler handler, TextWriter writer, Boolean preserveForm, Boolean setPreviousPage, VirtualPath path, VirtualPath filePath, String physPath, Exception error, String queryStringOverride)
— End of inner exception stack trace —
at System.Web.HttpServerUtility.ExecuteInternal(IHttpHandler handler, TextWriter writer, Boolean preserveForm, Boolean setPreviousPage, VirtualPath path, VirtualPath filePath, String physPath, Exception error, String queryStringOverride)
at System.Web.HttpServerUtility.Execute(String path, TextWriter writer, Boolean preserveForm)
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at System.Web.HttpServerUtility.Transfer(String path)
at BAMPortal.navbar_ascx.TreeViewNav_NodeClicked(Object sender, TreeNodeEventArgs eventArgs)
at Microsoft.BizTalk.Bam.Portal.ClickableTreeView.OnTreeNodeClicked(TreeNode node)
at Microsoft.BizTalk.Bam.Portal.ClickableTreeView.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.TreeView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.pages_view_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Observation: much information … yet without giving me many tips on the problem that was happening.

Actually the first error is what brings us a better sense of the error… it’s a permission problem to access the view… but why? and what was really causing this problem?

CAUSE
  • BAM Management Utility (BM.exe) doesn’t provide the capability to grant group permissions to BAM views instead you need to use user’s accounts. So these issues may occur if the user account which was granted permission to BAM objects are deleted from Active Directory or from Local Computers.

When you access to a certain view in BAM Portal the services invoked by the Portal will try to check the permission for ALL the users associated to this specific view and not only my user, so if a user was deleted from AD or from the local computer without first removing it from the view, the services will fail while attempting to map the account name with Security ID and you will get with this annoying problem: “Data is Null. This method or property cannot be called on Null values.”

You may experience any one or more of the following symptoms:

  • When you access to certain views in the BAM Portal
  • When you try to execute any kind of operation using BM.exe tool against certain views, like “bm.exe get-accounts”; “bm.exe remove-account” or “bm.exe remove-view”
SOLUTION
  • You have to manually delete those user accounts from SQL Server.
How can I really solved this problem?

So now I know the problem, the cause and the solution… but is it that simple?
… NO of course!

First problem: How can I really know with account(s) is causing the problem?

You can have many users in your organization associated with the view and may have passed months or years since the last time we associate the users account to this view… so it is really a problem to find with user is causing the problem.

  • Option 1: you can ask!
    • I know that if I ask who was deleting accounts in the AD or which accounts have been deleted, I will get the typical response… no one or no account has been deleted!!! Don’t go there is an endless road.
  • Option 2: You can use BM.exe!
    • Unfortunately we also can’t use BM tool to ask with account have permission to this view (bm.exe get-accounts), we get the following error:
      • ERROR: Failed to list permissions for BAM view.
        Data is Null. This method or property cannot be called on Null values.
  • Option 3: See in the documentation
    • Another endless road Sorriso. This type of documentation should exist, but few companies actually have them!

I finally decided to try to make a query directly in the BAM Primary Import database (BAMPrimaryImport) to attempt to get a list of all the account that were associated with this view in order to validate with my system administrators if they all existed in AD, this was the result:

WITH Query AS (
SELECT
    [UserName] = CASE princ.[type]
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = princ.[name],
    [Role] = null,
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --database user
    sys.database_principals princ
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
    [UserName] = CASE memberprinc.[type]
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = memberprinc.[name],
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Roles
    sys.database_principals roleprinc
LEFT JOIN
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
JOIN
    --All objects
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
)
SELECT * From  Query
WHERE Role like '%name_of_the_view%' AND ObjectType like 'View'

BAM-query-result

It may not be sophisticated or the best way but it served my purposes and was much easier and effective than be looking one by one in the SQL Server management Studio.

Now that I had a list of users, it was easy to validate with the system administrators which account had been deleted.

After so much effort and work trying to find the user that was causing the problem I found a simple and more effective solution to this problem in this post on MSDN Blogs: BizTalk Error: BAM Management Utility Errors, using this query:

USE BAMPrimaryImport
GO
Select Name,SID,SUser_SName(SID) as UserAccount from sysusers
WHERE ISLogin = 1 AND issqluser = 0 AND isntuser = 1

In the result for the DB roles, the third column for Windows users (and groups) should display the User Name (or group name) and can’t be null.

So if you execute the above query it will give the user(s) account(s) which was deleted from Domain Controller or Local Computer.

Second problem: How can I manually delete the account?

Yep … this question may seem a bit ridiculous … but the truth is, manually delete the account was not so simple after all… at least for me it wasn’t.

You can’t just delete that account from SQL Server. To do that you need to follow the steps:

  • Go to SQL Management Studio, expand the BAMPrimaryImport Database
  • Go to Security –> Roles –> Database Roles –> BAM_ManagementWS
  • Right click BAM_ManagementWS and go to properties option which will open a new window (Database Role Properties – BAM_ManagementWS)

BAM-ManagementWS

  • In that window go to Securables, choose the NT User account which needs to be deleted.

BAM-ManagementWS-properties-Securables

  • In the bottom portion you will see that NT User Account having “View Definition” Permission. Uncheck that option then click OK.
  • Now you should able to delete the user from Security –> User –> <NT User Account>

BAM-User-delete

However if you try to delete the user without following these steps, like I did :), you get stuck in another huge problem:

TITLE: Microsoft SQL Server Management Studio
——————————
Drop failed for User ‘domain\user’.  (Microsoft.SqlServer.Smo)
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
The database principal has granted or denied permissions to objects in the database and cannot be dropped. (Microsoft SQL Server, Error: 15284)

So we need now to manual revoke the Grant access to BAM_ManagementWS by execute the following query:

REVOKE VIEW DEFINITION ON USER::[domain\user] TO [BAM_ManagementWS] AS [domain\user]
GO
Security Considerations for the BAM Portal

Using the principle of least privilege, user accounts should have restrictive permissions to perform routine tasks in the BAM portal. Keep the following points in mind as you set up your user accounts for BAM to balance security with appropriate access for users.

User accounts

User accounts with minimum permissions are not able to use the BAM portal distributed navigation feature. To be able to use this feature, these accounts must have sufficient permissions to allow access to the Web services on the remote computer as well as on the local computer.

User accounts for the BAM Web services must have permissions to access all referenced databases and must be a member of the BAM_ManagementWS role in the referenced databases.

For the following user types, you should be aware of these considerations:

  • Domain Users: These users must have access permissions on remote computers that host BAM Primary Import databases that are being accessed.
  • Local User: Users who are assigned this role cannot use distributed navigation.
Administrator accounts

Administrators must be members of the securityadmin or sysadmin groups to grant permissions to domain users.

To run the BAM Management utility, you must be at least a database operator for the BAM databases.

Final Notes

I would like to thank Nino Crudele for the help that he gave me to solve this problem and to my coworker and friend José Barbosa who helped me creating these SQL scripts and as result solving the problem.

And a final note for all system administrator… please don’t delete Active Directory accounts… instead disable them!!

It’s very common for orchestrations to access databases or executing stored procedures. Whether this access is done by SQL Adapter, WCF-SQL adapter or through C # code if we are not careful to set the user permissions properly in the database, we can get one of these errors:

Cannot open database ‘…’ requested by the login. The login failed

or:

The EXECUTE permission was denied on the object ‘…’, database ‘…’, schema ‘dbo’

CAUSE

This problem occurs because the user account that you used to access database or that tries to execute stored procedure does not have sufficient permissions.

SOLUTION

You need to grant permission to the user to access database and/or to execute stored procedure.

Normally orchestrations are running under BizTalk Application Users group but you have to check the windows group configured on the host instance that runs the orchestrations which want to access the database.

How to grant reader/writer access to SQL database:

  • In SQL Server Management Studio, open Object Explorer and expand it to the database that you want to give access
  • Right-click the Security folder, point to New, and then click User.

SQL-Add-User-login

  • On the General page, enter user name and login in the Login name box.
  • Set the following Database role membership:
    • db_datareader
    • db_datawriter

How-grant-reader-writer-access-SQL-database

  • Click OK.

Granting execute permissions to all stored procedures in a database:

  • In SQL Server Management Studio, open Object Explorer and expand it to the database that you want to give execute access
  • Right-click the database name and then click Properties.
  • In Permissions tab
    • Select the user in Users or roles panel
    • And on Permissions for user panel check grant for Exetute permission

Granting-execute-permissions-to-all-stored-procedures-in-database

Tags: BizTalk | SQL | Errors and Warnings, Causes and Solutions