Database Experimentation Assistant Issue deploying DACPAC when creating Analysis Reports
I have been trying to set up this tool to test upgrading our database compatibility from 2008 to 2016. I have managed to Capture and Replay traces, but when I am trying to create a new Report I am getting the below error. SQL and tools are all installed on their own instance of Windows Server 2016 (Which is running in Hyper-V on a Windows 10 machine) 1. SQL Server 2016 2. Database Experimentation Assistant V 2.6 3. All SQL Services all running under the same domain account which is an Administrator Group of the server, along with being a sysadmin of the SQL instance. 4. Firewall settings are disabled on the server. 5. When creating a report I have selected 'Trust server certificate' Any help would be greatly appreciated, thanks. DEA Information: 0 : TraceEvent, 06/30/2023 05:10:01, Data: {"Message":"AppStarted","Timestamp":"2023-06-30T05:10:01.9515432Z"} DEA Information: 0 : TraceEvent, 06/30/2023 05:10:02, Data: {"Message":"OS:Microsoft Windows NT 6.2.9200.0, App name:DEA.exe, App version:2.6.61459.2","Timestamp":"2023-06-30T05:10:02.1703349Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:02, Data: {"Message":"Entered GetNewVersion","Timestamp":"2023-06-30T05:10:02.3734194Z"} DEA Information: 0 : MetricEvent, 06/30/2023 05:10:04, Data: {"IsSuccess":false,"Name":"Duration","StartTime":"2023-06-30T05:10:02.3734194Z","Value":2129.6073,"Operation":"GetNewVersion","Timestamp":"2023-06-30T05:10:04.5051926Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:04, Data: {"Message":"Exited GetNewVersion","Timestamp":"2023-06-30T05:10:04.5208116Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:12, Data: {"Message":"Entered GetSqlServerConnectionMetaData","Timestamp":"2023-06-30T05:10:12.322225Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:12, Data: {"Message":"Attempting to get the SQL server metadata.","Operation":"SqlServerConnectionMetadataModel","Properties":{"ProductVersion":null,"ServerRoleName":null,"Id":"67148c5f-2cc7-48b7-8bb5-540a239766b9"},"Timestamp":"2023-06-30T05:10:12.3377877Z"} DEA Information: 0 : SystemInformationEvent, 06/30/2023 05:10:12, Data: {"Properties":{"TotalPhysicalMemoryKB":"3241524","AvailablePhysicalMemoryKB":"1598172","TotalPageFileKB":"4290100","AvailablePageFileKB":"2110960","SystemCacheKB":"1347520","CPUCount":"1","HyperthreadRatio":"1","StackSizeKB":"2044","MaxWorkersCount":"512","SchedulerCount":"1","SchedulerTotalCount":"8","SqlServerStartTimeMsTicks":"80958155","CurrentTime":"30/06/2023 2:40:12 PM","Id":"67148c5f-2cc7-48b7-8bb5-540a239766b9"},"Timestamp":"2023-06-30T05:10:12.7327129Z","Id":"67148c5f-2cc7-48b7-8bb5-540a239766b9"} DEA Information: 0 : TraceEvent, 06/30/2023 05:10:12, Data: {"Message":"Successfully retrieved SQL server metadata.","Operation":"SqlServerConnectionMetadataModel","Properties":{"ProductVersion":"13.0.6404.1","ServerRoleName":"sysadmin","Id":"67148c5f-2cc7-48b7-8bb5-540a239766b9"},"Timestamp":"2023-06-30T05:10:12.7367113Z"} DEA Information: 0 : MetricEvent, 06/30/2023 05:10:12, Data: {"IsSuccess":false,"Name":"Duration","StartTime":"2023-06-30T05:10:12.322225Z","Value":406.6709,"Operation":"GetSqlServerConnectionMetaData","Timestamp":"2023-06-30T05:10:12.7377494Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:12, Data: {"Message":"Exited GetSqlServerConnectionMetaData","Timestamp":"2023-06-30T05:10:12.7377494Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:39, Data: {"Message":"Entered GetSqlServerConnectionMetaData","Timestamp":"2023-06-30T05:10:39.0789001Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:39, Data: {"Message":"Attempting to get the SQL server metadata.","Operation":"AnalysisModel","Properties":{"ProductVersion":null,"Version":"2.6.61459.2","SchemaVersion":"1.3","CreatedDate":"30/06/2023 2:40:39 PM","IsSchemaUpgradeRequired":"False","ProgressPercentage":"0","ErrorCode":"0","Id":"e6c397c6-fb1b-43c6-bcf0-faf60942761c"},"Timestamp":"2023-06-30T05:10:39.0899005Z"} DEA Information: 0 : SystemInformationEvent, 06/30/2023 05:10:39, Data: {"Properties":{"TotalPhysicalMemoryKB":"3241524","AvailablePhysicalMemoryKB":"1535584","TotalPageFileKB":"4290100","AvailablePageFileKB":"2066060","SystemCacheKB":"1427008","CPUCount":"1","HyperthreadRatio":"1","StackSizeKB":"2044","MaxWorkersCount":"512","SchedulerCount":"1","SchedulerTotalCount":"8","SqlServerStartTimeMsTicks":"80958155","CurrentTime":"30/06/2023 2:40:39 PM","Id":"e6c397c6-fb1b-43c6-bcf0-faf60942761c"},"Timestamp":"2023-06-30T05:10:39.0968998Z","Id":"e6c397c6-fb1b-43c6-bcf0-faf60942761c"} DEA Information: 0 : TraceEvent, 06/30/2023 05:10:39, Data: {"Message":"Successfully retrieved SQL server metadata.","Operation":"AnalysisModel","Properties":{"ProductVersion":"13.0.6404.1","Version":"2.6.61459.2","SchemaVersion":"1.3","CreatedDate":"30/06/2023 2:40:39 PM","IsSchemaUpgradeRequired":"False","ProgressPercentage":"0","ErrorCode":"0","Id":"e6c397c6-fb1b-43c6-bcf0-faf60942761c"},"Timestamp":"2023-06-30T05:10:39.097902Z"} DEA Information: 0 : MetricEvent, 06/30/2023 05:10:39, Data: {"IsSuccess":false,"Name":"Duration","StartTime":"2023-06-30T05:10:39.0799004Z","Value":30.503700000000002,"Operation":"GetSqlServerConnectionMetaData","Timestamp":"2023-06-30T05:10:39.1099012Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:39, Data: {"Message":"Exited GetSqlServerConnectionMetaData","Timestamp":"2023-06-30T05:10:39.1099012Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:39, Data: {"Message":"Entered VerifyUserServerRoleName","Timestamp":"2023-06-30T05:10:39.1109014Z"} DEA Information: 0 : MetricEvent, 06/30/2023 05:10:39, Data: {"IsSuccess":false,"Name":"Duration","StartTime":"2023-06-30T05:10:39.1109014Z","Value":1.2535,"Operation":"VerifyUserServerRoleName","Timestamp":"2023-06-30T05:10:39.1120428Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:39, Data: {"Message":"Exited VerifyUserServerRoleName","Timestamp":"2023-06-30T05:10:39.1120428Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:39, Data: {"Message":"Entered PublishDacPac","Timestamp":"2023-06-30T05:10:39.1120428Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:39, Data: {"Message":"Setting up Analysis database.","Timestamp":"2023-06-30T05:10:39.1139012Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:39, Data: {"Message":"SqlPackage.exe found from the path [C:\\Program Files (x86)\\Microsoft SQL Server\\140\\DAC\\bin\\SqlPackage.exe] specified in the configuration.","Timestamp":"2023-06-30T05:10:39.116901Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:45, Data: {"Message":"Process C:\\Program Files (x86)\\Microsoft SQL Server\\140\\DAC\\bin\\SqlPackage.exe /Action:Publish /SourceFile:\"ExperimentationDatabase.dacpac\" /TargetDatabaseName:\"Analysistest1638236986390639009\" /TargetServerName:\"CI10281-W2016\" /Variables:\"DatabaseA\"=\"Atest1638236986390639009\" /Variables:\"DatabaseB\"=\"Btest1638236986390639009\" /Variables:\"ReportName\"=\"test1\" /Variables:\"DEAVersion\"=\"2.6.61459.2\" /Variables:\"SchemaVersion\"=\"1.3\" /p:BlockOnPossibleDataLoss=False completed with exit code: 1","Timestamp":"2023-06-30T05:10:45.5430174Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:45, Data: {"Message":"Process output: ","Timestamp":"2023-06-30T05:10:45.5430174Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:45, Data: {"Message":"Error occurred while running process. Command: \"C:\\Program Files (x86)\\Microsoft SQL Server\\140\\DAC\\bin\\SqlPackage.exe /Action:Publish /SourceFile:\"ExperimentationDatabase.dacpac\" /TargetDatabaseName:\"Analysistest1638236986390639009\" /TargetServerName:\"CI10281-W2016\" /Variables:\"DatabaseA\"=\"Atest1638236986390639009\" /Variables:\"DatabaseB\"=\"Btest1638236986390639009\" /Variables:\"ReportName\"=\"test1\" /Variables:\"DEAVersion\"=\"2.6.61459.2\" /Variables:\"SchemaVersion\"=\"1.3\" /p:BlockOnPossibleDataLoss=False\", Error:\"*** Could not deploy package.\r\nUnable to connect to target server 'CI10281-W2016'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server.\r\nA connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)\r\nThe certificate chain was issued by an authority that is not trusted.\r\n\r\n\"","Timestamp":"2023-06-30T05:10:45.5430174Z"} DEA Information: 0 : DiagnosticEvent, 06/30/2023 05:10:45, Data: {"Message":"DacPac publishing failed.","Timestamp":"2023-06-30T05:10:45.5430174Z"} DEA Information: 0 : MetricEvent, 06/30/2023 05:10:45, Data: {"IsSuccess":true,"Name":"Duration","StartTime":"2023-06-30T05:10:39.1120428Z","Value":6432.3149,"Operation":"PublishDacPac","Timestamp":"2023-06-30T05:10:45.5450167Z"} DEA Error: -2146233088 : ExceptionEvent, 06/30/2023 05:10:45, Data: {"Code":-2146233088,"Message":"Failed to generate new analysis report.","Operation":"AnalysisModel","Properties":{"ProductVersion":"13.0.6404.1","Version":"2.6.61459.2","SchemaVersion":"1.3","CreatedDate":"30/06/2023 2:40:39 PM","IsSchemaUpgradeRequired":"False","ProgressPercentage":"6","ErrorCode":"1004","Id":"e6c397c6-fb1b-43c6-bcf0-faf60942761c"},"Timestamp":"2023-06-30T05:10:45.5530169Z"}, Exception: [Exception Info]:Microsoft.DEA.Common.Exceptions.DeaException, Message:Exception of type 'Microsoft.DEA.Common.Exceptions.DeaException' was thrown. StackTrace: at Microsoft.DEA.BusinessLogic.Actions.PublishDacPac.Run() at Microsoft.DEA.BusinessLogic.ActionDecorator.<>c__DisplayClass2_0.<Run>b__0() at System.Threading.Tasks.Task.InnerInvoke() at System.Threading.Tasks.Task.Execute() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.DEA.BusinessLogic.ActionDecorator.<Run>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at Microsoft.DEA.BusinessLogic.ActionDecorator.<Run>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) at Microsoft.DEA.BusinessLogic.Bootstrappers.NewAnalysisXEvents.<RunAsync>d__21.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.DEA.BusinessLogic.Bootstrappers.NewAnalysisXEvents.<RunAsync>d__21.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.DEA.BusinessLogic.BootstrapperFactory.<StartWorkflowAsync>d__37.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.DEA.BusinessLogic.BootstrapperFactory.<CreateNewAnalysisBootstrapperAsync>d__28.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.DEA.UI.ViewModels.MainRegion.NewReportViewModel.<StartNewAnalysisAsync>d__113.MoveNext()Do it right! Deploying SQL Server R Services on computers without Internet access
First published on MSDN on Oct 20, 2016 Reviewed by: Sanjay Mishra, Umachandar Jayachandran, Dimitri Furman, Jeannine Nelson-Takaki, Joe Sack, Kun Cheng, Eric BurgessOne of the most exciting features in SQL Server 2016 is R Services (in-database).
