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?