Why is Smo.Agent.JobServer is changing 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

This is by design. Unlike OLE DB or ADO, the connection string that is returned by SqlConnection.ConnectionString is the same as the user-set ConnectionString, minus security information if the Persist Security Info value is set to false (default). The .NET Framework Data Provider for SQL Server does not persist or return the password in a connection string unless you set Persist Security Info to true.

Microsoft strongly recommends that Persist Security Info be left as false. If you are supplying a userid and password when making a connection, you are most protected if that information is used to open the connection, and then discarded. This is especially important if you are supplying an open connection to an untrusted source or persisting connection information to disk. Keeping Persist Security Info as false helps ensure that the untrusted source does not have access to the security-sensitive information for your connection and also helps ensure that no security-sensitive information is persisted to disk with your connection string information.