Create a ServerConnection object to create an SMO server object in C#?

364 Views Asked by At

I'm trying to establish a connection to SQL server to use SMO. In my local machine with option1, I'm able to connect with option 1. However I have to run the same in a server machine (with SQL Server and not SQL Server Express). I'm running as a Windows service in the server machine which should connect to SQL server. I tried the below options and multiple combinations I can think for connection string.

ServerConnection connection = GetServerConnection();
Server sqlServer = new Server(connection);

private ServerConnection  GetServerConnection()
{
  //Option 1
 ServerConnection connection = new ServerConnection("localhost\\SQLEXPRESS");

  //Option2
  ServerConnection connection =new ServerConnection("localhost");

  //Option3
  ServerConnection connection =new ServerConnection("DEVSRV02");

  //Option4
  ServerConnection connection =new ServerConnection();

  //Option5
  //string conStr1="Server=DEVSRV02;Initial Catalog=master;Integrated Security=SSPI;";
  //string conStr2="Data Source="(local)";Server=DEVSRV02;InitialCatalog=master; Trusted_Connection=True";
  //string conStr3="Dsn=AuditLog;uid=xxx;trusted_connection=Yes";
  //string conStr4="Driver={ODBC Driver 17 for SQL Server};Server=DEVSRV02;Database=AuditLog;Uid=xxx; Pwd=xxx;";
  //string conStr5="Server=DEVSRV02;Initial Catalog=AuditLog;Integrated Security=SSPI;";
  //string conStr6="Server=localhost;Initial Catalog=AuditLog;Integrated Security=SSPI;";
  //string conStr7="Data Source=DEVSRV02;InitialCatalog=master; Trusted_Connection=True";

Microsoft.Data.SqlClient.SqlConnection sqlCon = new Microsoft.Data.SqlClient.SqlConnection(conStr);
ServerConnection connection = new ServerConnection(sqlCon);

return connection ;
}

With the username and password I used in connection string, I could successfully login using windows authentication option in SSMS manually. I also created another login using SQL Server Authentication and no password, with which I'm able to login to SSMS manually. However, I tried both these options in connection strings, and every time, I'm getting the below exception:

System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Data.SqlClient.SqlConnection.get_DataSource()
   at Microsoft.SqlServer.Management.Common.ConnectionSettings.InitFromSqlConnection(SqlConnection sc)
   at Microsoft.SqlServer.Management.Common.ConnectionManager..ctor(SqlConnection sqlConnectionObject, IRenewableToken accessToken)
   at Microsoft.SqlServer.Management.Common.ServerConnection..ctor(SqlConnection sqlConnection, IRenewableToken accessToken)
   at EmailReader.SiteSpecificDatabaseBuilder.GetServerConnection()

Any hints on what I'm missing?

0

There are 0 best solutions below