Why is Smo.Agent.JobServer altering my SQL connection string?

Question

Hi I'm noticing some odd behavior while using SMO and was wondering if anyone could provide some insight into this; when I first make any call to the Server.JobServer property (in this case, merely calling .ToString() which presumably lazy initializes it in the background) I find that it's affected my SQL connection string - specifically the password has disappeared!

Below is the code I use to produce this behavior:

SqlConnection conn = new SqlConnection(@"Data Source=myserver;Initial Catalog=Stage;user=myuser;password=abc;");    
ServerConnection serverConn = new ServerConnection(conn);    
Server server = new Server(serverConn);    
Console.WriteLine(conn.ConnectionString);    
server.JobServer.ToString();    
Console.WriteLine(conn.ConnectionString);

The output from this is
Data Source=myserver;Initial Catalog=Stage;user=myuser;password=abc;
Data Source=myserver;Initial Catalog=Stage;user=myuser;

The account myuser is configured with the public and sysadmin SQL Server Roles and has dbo on master and msdb. Could anyone provide any insight or clues as to why this is happening?

Answer 1

Hi Philip York,

I am able to reproduce this issue according to your description. The connection string doesn’t change, and it is only related the display of the security information. To guarantee that the user-set properties on the ConnectionString is fully displayed , it is required to set the value of ‘Persist Security Info’ to true:

SqlConnection conn = new SqlConnection(@"Persist Security Info=True;Data Source=myserver;Initial Catalog=Stage;user=myuser;password=abc;");    

For more information: SqlConnection.ConnectionString Property.

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support

Answer 2

Ahh, thank you for clarifying this!