Using Microsoft Metadata Samples Remotely

Everything documented in this post was a complete waste of time as you can launch DependencyViewer from the command line using the /depDb: switch to specify the remote repository. Example below

DependencyViewer.exe /depDb:"Server=myservername;database=SSIS_META;Integrated Security=SSPI;"

This switch is documented for DependencyAnalyzer, but there are no such instructions about command line usage of DependencyViewer except when you launch it from the command line with the standard /? switch. Must learn to mentally join up very widely spaced dots...

Microsoft provides sample code showing how to interrogate the metadata of the SQL Server 2005 tool suite. The process is as follows:

• Run scripts to create the SSIS_META database
• Run DependencyAnalyzer on your warehouse, which populates SSIS_META with lots of juicy metadata
• Use DependencyViewer to view the metadata in a graphical layout

I spotted an SSIS_META database on a remote server and naturally wanted to peruse its contents with DependencyViewer. However DependencyViewer assumes a local repository, so the person who created this database either had local login rights to the server or had a modified build that could connect to remote repositories. I had neither, so I set about making the necessary changes to the solution.

Form1.cs has a hard coded connection string:

private string connectionString = "server=localhost;database=SSIS_META;Integrated Security=SSPI;";

which I changed to:

private string connectionString = "server=myservername;database=SSIS_META;Integrated Security=SSPI;";

The rebuilt app threw an error on clicking the Load button as follows:

************** Exception Text **************System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=gfjhs98s90s67a6dssd' failed. at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.PermissionSet.Demand() at System.Data.Common.DbConnectionOptions.DemandPermission() at System.Data.SqlClient.SqlConnection.PermissionDemand() at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.Samples.DependencyViewer.Form1.buttonTest_Click(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
The action that failed was:Demand
The type of the first permission that failed was:System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:Intranet

I was trying to run it from a network share, never a good idea - so I copied the exe to a local location. Next the load button gave me an hourglass for a while before throwing the following:

************** Exception Text **************System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.Samples.DependencyViewer.Form1.buttonTest_Click(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

I put the connection string in a message box in at the start of ButtonTest_Click and found that the connection string was still pointing to localhost. More searching turned up another reference in Program.cs. The following change in this file got it connecting.

From:

[Argument(ArgumentType.AtMostOnce, HelpText = "ADO.Net SqlConnection compatible connection string to dependency database location.", DefaultValue = "Server=localhost;database=SSIS_Meta;Integrated Security=SSPI;")]
public string depDb;

To:

[Argument(ArgumentType.AtMostOnce, HelpText = "ADO.Net SqlConnection compatible connection string to dependency database location.", DefaultValue = "Server=myservername;database=SSIS_Meta;Integrated Security=SSPI;")]public string depDb;