Saturday, March 31, 2012

MDSModelDeploy.exe Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.


I was trying to automate the creation and deployment of my MDS model and came across the following issue.

If you try to run the following powershell command

invoke-command -computername Servername -scriptblock {& 'C:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration\MDSModelDeploy.exe' createpackage -model TestModel  -package c:\Data\LocalBackup\TestModelackage.pkg -version VERSION_1 -service MDS2 -includedata}

you get the following error
Creating a package for model MTN
MDSModelDeploy operation failed. Elapsed time: 00:00:06.4551044
Error:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

I enabled trace on MDSModelDeploy

Error Log
MDS Information: 0 : Attempting to execute command: MDSModelDeploy createpackage -model TestModel -package c:\Data\LocalBackup\TestModelackage.pkg -version VERSION_1 -service MDS2 -includedata
    DateTime=2012-03-31T10:35:02.3896659Z
MDS Error: 0 : Service started successfully,  Assembly version: 11.0.0.0, file version: 11.0.2100.60 ((SQL11_RTM).120210-1917 )
    DateTime=2012-03-31T10:35:10.6863285Z
MDS Error: 0 : ApiContractVersion: 5102
    DateTime=2012-03-31T10:35:10.6863285Z
MDS Start: 1 : Service.InitializeExpirationStatus
    DateTime=2012-03-31T10:35:10.6863285Z
MDS Information: 0 : Evaluation period days remaining code: 0
    DateTime=2012-03-31T10:35:10.7488269Z
MDS Stop: 2 : Service.InitializeExpirationStatus
    DateTime=2012-03-31T10:35:10.7488269Z
MDS Start: 1 : Deployment: getting list of models
    DateTime=2012-03-31T10:35:10.7644515Z
MDS Start: 1 :
                  Begin operation: ExecuteRequest
                  Host info:       Microsoft.MasterDataServices.Core.ExplicitHostContext
                  User Name:       Domain\username
                  Time:            03/31/2012 21:35:10
    DateTime=2012-03-31T10:35:10.9050729Z
MDS Verbose: 0 : Request message:
<MetadataGetRequest xmlns="http://schemas.datacontract.org/2004/07/Microsoft.MasterDataServices.Services.MessageContracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <International i:nil="true" xmlns:a="http://www.w3.org/2005/09/ws-i18n" />
  <ResultOptions xmlns:a="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09">
    <a:Models>Identifiers</a:Models>
  </ResultOptions>
  <SearchCriteria xmlns:a="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09">
    <a:AttributeGroups />
    <a:Attributes />
    <a:DerivedHierarchies />
    <a:DerivedHierarchyLevels />
    <a:Entities />
    <a:ExplicitHierarchies />
    <a:MemberTypes />
    <a:Models />
    <a:VersionFlags />
    <a:Versions />
  </SearchCriteria>
</MetadataGetRequest>
    DateTime=2012-03-31T10:35:11.0613189Z
MDS Start: 1 : RequestContext.InitializeRequestContext
    DateTime=2012-03-31T10:35:11.0613189Z
MDS Start: 1 : RequestContext.InitializeDatabaseContext
    DateTime=2012-03-31T10:35:11.0613189Z
MDS Stop: 2 :
                  End  operation: MetadataGet
                  Host info:       Microsoft.MasterDataServices.Core.ExplicitHostContext
                  User Name:       [Domain]\[Username]
                  Time:            03/31/2012 21:35:11
    DateTime=2012-03-31T10:35:11.2956879Z
MDS Verbose: 0 : Response message:
<MetadataGetResponse xmlns="http://schemas.datacontract.org/2004/07/Microsoft.MasterDataServices.Services.MessageContracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <OperationResult xmlns:a="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09">
    <a:Errors />
    <a:RequestId>d8cedd0f-34cd-4b43-b43b-e0fd860bb44d</a:RequestId>
  </OperationResult>
  <Metadata xmlns:a="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09">
    <a:AttributeGroups />
    <a:Attributes />
    <a:DerivedHierarchies />
    <a:DerivedHierarchyLevels />
    <a:Entities />
    <a:ExplicitHierarchies />
    <a:MemberTypes />
    <a:Models />
    <a:VersionFlags />
    <a:Versions />
  </Metadata>
</MetadataGetResponse>
    DateTime=2012-03-31T10:35:11.4206847Z
MDS Error: 0 :
    DateTime=2012-03-31T10:35:11.4206847Z
MDS Error: 0 : MDSModelDeploy operation failed. Elapsed time: 00:00:09.1523012
    DateTime=2012-03-31T10:35:11.4206847Z
MDS Error: 0 : Error:
    DateTime=2012-03-31T10:35:11.4206847Z
MDS Error: 0 : System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
   at Microsoft.MasterDataServices.Core.DataAccess.DbHelper.ExecuteNonQueryHandlingTransportError(SqlCommand sqlCommand)
   at Microsoft.MasterDataServices.Core.DataAccess.DbHelper.GetTimeout(String settingName)
   at Microsoft.MasterDataServices.Core.DataAccess.DbHelper.SetConnection(String connectionString)
   at Microsoft.MasterDataServices.Core.BusinessEntities.RequestContext.InitializeDatabaseContext()
   at Microsoft.MasterDataServices.Core.BusinessEntities.RequestContext.InitializeRequestContext(HostContext hostContext)
   at Microsoft.MasterDataServices.Services.Service.MetadataGet(MetadataGetRequest request)
   at Microsoft.MasterDataServices.WebUI.ServiceAdapter.ExecuteRequest[TRequestType,TResponseType](MdmServiceOperation`2 operation, TRequestType request)
   at Microsoft.MasterDataServices.WebUI.ServiceAdapter.DoMetadataGet(MetadataSearchCriteria searchCriteria, MetadataResultOptions resultOptions)
   at Microsoft.MasterDataServices.Deployment.ModelReader.GetModels(Boolean isAdminOnly)
   at Microsoft.MasterDataServices.Deployment.Utility.ModelDeploy.CreatePackage(String serviceName, String packageFile, String modelName, String versionName, Boolean includeData)
   at Microsoft.MasterDataServices.Deployment.Utility.ModelDeploy.Main(String[] args)
    DateTime=2012-03-31T10:35:11.4206847Z


After using reflector I managed to find the code that is causing the issue
private void InitializeRequestContext(HostContext hostContext)
{
    Log.WriteStart("RequestContext.InitializeRequestContext");
    if (hostContext == null)
    {
        throw new ArgumentNullException("hostContext");
    }
    this.HostContext = hostContext;
    this.InitializeDatabaseContext();
    this.InitializeCurrentUser();
    Log.WriteStop("RequestContext.InitializeRequestContext");
}
Not sure if calling InitializeCurrentUser before InitializeDatabaseContext will fix the problem. As I didn’t have to time to decompile all the MDS code and try to get it to compile.

Work around
Is to set the connection string to sql login instead of Integrated Security
from        <add name="MDS2" connectionString="Data Source=servername;Initial Catalog=database;Integrated Security=True;Connect Timeout=60" />
to            <add name="MDS2" connectionString="Data Source=servername;Initial Catalog=database;User Id=sqlloginuser;Password=password;Connect Timeout=60" />


To test that winrm is working correctly run the following command
invoke-command -computername yourserver -scriptblock {& 'C:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration\MDSModelDeploy.exe' listservices}
Result
MDS services (Service, Website, Virtual Path):
MDS1, Website1_DEV,
MDS2, Website2_DEV,

MDSModelDeploy operation completed successfully. Elapsed time: 00:00:06.7047133

Wednesday, March 14, 2012

MDS (Master Data Services) SQL 2012 installing on SQL 2008 R2

MDS (Master Data Services) SQL 2012 is out with new enhancements to make development a lot easier. The issue is, is business ready to move to SQL 2012... So I took a shot and tried installing MDS SQL 2012 on SQL 2008 R2 instance.
Steps
1. install MDS SQL 2012 from ISO
2.Run Microsoft SQL Server 2012\Master Data Services\Master Data Services Configuration Manager.exe
3. Create new database point to SQL 2008 R2 instance.
4. Create new Web page

Everything run smoothly and portal came up.

http://msdn.microsoft.com/en-us/library/gg488708(v=sql.110).aspx#noengine

Thursday, March 8, 2012

Master Data Services (MDS) Clean Model of Carriage Return and New line


DECLARE @sqlquery NVARCHAR(4000)
DECLARE RemoveCr CURSOR FOR
SELECT DISTINCT --m.Name, e.Name, e.EntityTable, a.DisplayName,a.TableColumn, e.*,
'UPDATE mdm.' + e.EntityTable + ' SET ' + a.TableColumn + ' = SUBSTRING(' + a.TableColumn + ', 0, CHARINDEX ( CHAR(13), ' + a.TableColumn + ' ) ) WHERE ' + a.TableColumn + ' LIKE ''%'' + CHAR(13) + CHAR(10)+ ''%'''
--'SELECT ' + a.TableColumn + ' FROM mdm.' + e.EntityTable + ' WHERE ' + a.TableColumn + ' LIKE ''%'' + CHAR(13) + CHAR(10)+ ''%'''
FROM mdm.tblEntity e
INNER JOIN mdm.tblModel m ON e.Model_ID = m.ID
INNER JOIN mdm.tblModelVersion mv ON m.ID = mv.Model_ID
INNER JOIN mdm.tblAttribute a ON a.Entity_ID = e.ID
INNER JOIN mdm.tblList lAtt ON a.AttributeType_ID = lAtt.OptionID AND lAtt.ListCode = 'lstAttributeType'
INNER JOIN mdm.tblList lDat ON a.DataType_ID = lAtt.OptionID AND lDat.ListCode = 'lstDataType'
WHERE m.Name <> 'Metadata'
AND mv.Name = 'VERSION_1'
AND lAtt.ListOption <> 'System'
AND lDat.ListOption = 'Text'
AND IsBase = 0
OPEN RemoveCr
FETCH NEXT FROM RemoveCr INTO @sqlquery
WHILE (@@FETCH_STATUS = 0)
BEGIN
 EXEC(@sqlquery)
 FETCH NEXT FROM RemoveCr INTO @sqlquery
END
CLOSE RemoveCr

DEALLOCATE RemoveCr

Master Data Services (MDS) Moving Subscription Views


SET NOCOUNT ON
-------------------------------------------------------------------------------
-- Change the following variables
-------------------------------------------------------------------------------
DECLARE @Server VARCHAR(100) = 'SERVERNAME'
DECLARE @OrginalDatabase VARCHAR(100) = 'MDSDev'
DECLARE @DestinationDatabase VARCHAR(100) = 'MDSProd'
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
DECLARE @execQuery VARCHAR(1000) = 'SELECT * INTO ##Views FROM OPENROWSET(''SQLNCLI'', ''server=' + @Server + ';trusted_connection=yes'', ''set fmtonly off exec ' + @OrginalDatabase + '.[mdm].[udpSubscriptionViewGet]'')'
EXEC(@execQuery)

DECLARE @View_Name NVARCHAR(200)
DECLARE @Model_Name NVARCHAR(200)
DECLARE @Entity_Name NVARCHAR(200)
DECLARE @query NVARCHAR(4000)
DECLARE db_Cursor CURSOR FOR

SELECT View_Name, Model_Name, Entity_Name FROM ##Views
OPEN db_Cursor
FETCH NEXT FROM db_Cursor INTO @View_Name, @Model_Name, @Entity_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @query = ''
SET @query = 'DECLARE @EntityName NVARCHAR(200) = ''' + @Entity_Name + '''' + CHAR(10)
SET @query = + @query + 'DECLARE @ModelName NVARCHAR(200) = ''' + @Model_Name + '''' + CHAR(10)
SET @query = + @query + 'DECLARE @Entity_ID int' + CHAR(10)
SET @query = + @query + 'DECLARE @Model_ID int' + CHAR(10)
SET @query = + @query + 'DECLARE @ModelVersion_ID int' + CHAR(10)
SET @query = + @query + 'DECLARE @ModelVersionFlag_ID int' + CHAR(10)
SET @query = + @query + 'DECLARE @Return_ID int' + CHAR(10)
SET @query = + @query + 'DECLARE @Return_MUID uniqueidentifier' + CHAR(10)
SET @query = + @query + CHAR(10)
SET @query = + @query + 'SELECT @Model_ID = m.id, @Entity_ID=e.ID, @ModelVersion_ID = mv.ID, @ModelVersionFlag_ID = mv.VersionFlag_ID FROM mdm.tblModel m' + CHAR(10)
SET @query = + @query + 'INNER JOIN mdm.tblEntity e ON m.ID = e.Model_ID' + CHAR(10)
SET @query = + @query + 'INNER JOIN mdm.tblModelVersion mv ON m.ID = mv.Model_ID' + CHAR(10)
SET @query = + @query + 'WHERE mv.Name = ''VERSION_1''' + CHAR(10)
SET @query = + @query + 'AND m.NAME = @ModelName' + CHAR(10)
SET @query = + @query + 'AND e.Name = @EntityName' + CHAR(10)
SET @query = + @query + CHAR(10)
SET @query = + @query + 'EXECUTE [' + @DestinationDatabase + '].[mdm].[udpSubscriptionViewSave]' + CHAR(10)
SET @query = + @query + '@SubscriptionView_ID=NULL' + CHAR(10)
SET @query = + @query + ',@Entity_ID = @Entity_ID' + CHAR(10)
SET @query = + @query + ',@Model_ID = @Model_ID' + CHAR(10)
SET @query = + @query + ',@DerivedHierarchy_ID = NULL' + CHAR(10)
SET @query = + @query + ',@ModelVersion_ID = @ModelVersion_ID ' + CHAR(10)
SET @query = + @query + ',@ModelVersionFlag_ID = @ModelVersionFlag_ID' + CHAR(10)
SET @query = + @query + ',@ViewFormat_ID = 1' + CHAR(10)
SET @query = + @query + ',@Levels = NULL' + CHAR(10)
SET @query = + @query + ',@SubscriptionViewName = ''' + @View_Name + '''' + CHAR(10)
SET @query = + @query + ',@Return_ID = @Return_ID OUTPUT' + CHAR(10)
SET @query = + @query + ',@Return_MUID = @Return_MUID OUTPUT' + CHAR(10)
SET @query = + @query + 'GO' + CHAR(10)
      PRINT @query
      FETCH NEXT FROM db_Cursor INTO @View_Name, @Model_Name, @Entity_Name

END
CLOSE db_Cursor
DEALLOCATE db_Cursor
DROP TABLE ##Views
GO

Master Data Services (MDS) Rebuild Subscription Views


--Build Delete Statements
SELECT 'EXEC mdm.udpSubscriptionViewDeleteByID @ID=' + CONVERT(VARCHAR(10), ID) + ' ,@DeleteView=1' FROM mdm.tblSubscriptionView  WHERE IsDirty = 1
--Build Create Statements
SELECT 'EXEC mdm.udpSubscriptionViewSave @SubscriptionView_ID=NULL,@Entity_ID='  + CONVERT(VARCHAR(10), Entity_ID) + ',@Model_ID='  + CONVERT(VARCHAR(10), Model_ID) + ',@DerivedHierarchy_ID=NULL,@ModelVersion_ID='  + CONVERT(VARCHAR(10), ModelVersion_ID) + ',@ModelVersionFlag_ID=NULL,@ViewFormat_ID='  + CONVERT(VARCHAR(10), ViewFormat_ID) + ',@Levels=NULL,@SubscriptionViewName=N''' + Name + '''' FROM mdm.tblSubscriptionView  WHERE IsDirty = 1