public void undeploy(bool isUninstall = false)
{
if (!isUninstall)
{
return;
}
var sqlSettings = this.DeployerSettings.castTo <SQLServiceSettings>();
var sqlServer = this.GetSqlServer(sqlSettings.id);
string keylogin = $"services.{sqlSettings.id}.username";
string keydatabase = $"services.{sqlSettings.id}.database";
// The database name, username and password must remain the same between deployments.
// If we generated new user/pwd for new deployment, rollback functionality would NOT work as expected
// as it would require re-deploying the logins.
var dbLogin = this.Deployment.GetRuntimeSetting(keylogin, null);
var dbDatabase = this.Deployment.GetRuntimeSetting(keydatabase, null);
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(sqlServer.connectionString);
var utils = new UtilsSqlServer(this.Logger);
var connection = utils.GetServerConnection(sqlServer.connectionString);
if (connection != null)
{
// Only remove if the database was autogenerated
if (sqlServer.databaseName.IsNullOrDefault())
{
utils.DeleteDatabase(connection, dbDatabase);
}
// Only remove if the login is not the same as the one in the master connection
if (builder.UserID != dbLogin)
{
utils.DeleteLogin(connection, dbLogin);
}
utils.DeleteLogin(connection, this.Deployment.WindowsUsernameFqdn(true));
}
}
/// <summary>
///
/// </summary>
public void deploy()
{
var sqlSettings = this.DeployerSettings.castTo <SQLServiceSettings>();
if (string.IsNullOrWhiteSpace(sqlSettings.id))
{
throw new Exception("SQL Service request id must have a value.");
}
// Figure out what SQLServer settings to use for this instance
var sqlServer = this.GetSqlServer(sqlSettings.id);
var id = this.Deployment.installedApplicationSettings.GetId() + "_" + sqlSettings.id;
// Keys to store username, password and databasename
string keylogin = $"services.{sqlSettings.id}.username";
string keypassword = $"services.{sqlSettings.id}.password";
string keydatabase = $"services.{sqlSettings.id}.database";
// Parse the connection string
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(sqlServer.connectionString);
// Make sure we can connect to the server
var utils = new UtilsSqlServer(this.Logger);
this.Logger.LogInfo(true, "Getting SQL connection '{0}'", sqlServer.connectionString);
var connection = utils.GetServerConnection(sqlServer.connectionString);
if (connection == null)
{
throw new Exception("Could not connect to the server: " + sqlServer.connectionString);
}
// The actual database name that will be used
string databaseName;
if (string.IsNullOrWhiteSpace(sqlServer.databaseName))
{
databaseName = "chf_" + id;
}
else
{
databaseName = sqlServer.databaseName;
}
this.Deployment.SetRuntimeSetting(keydatabase, databaseName);
if (string.IsNullOrWhiteSpace(databaseName))
{
throw new Exception("Database name cannot be empty or null.");
}
// Ensure we have database and login.
this.Logger.LogInfo(true, "Getting SQL database '{0}'", databaseName);
var database = utils.FindDatabase(connection, databaseName, true);
if (database == null)
{
throw new Exception("Could not find database " + databaseName);
}
if (!database.Status.HasFlag(smo.DatabaseStatus.Normal))
{
throw new Exception("Database should be in 'Normal' status. The current database status is not compatible with automated deployments: " +
database.Status);
}
// The database name, username and password must remain the same between deployments.
// If we generated new user/pwd for new deployment, rollback functionality would NOT work as expected
// as it would require re-deploying the logins.
string dbLogin;
string dbPassword;
// If this is a passthrough authentication, propagate credentials as-is
if (sqlServer.passThroughAuth)
{
dbLogin = builder.UserID;
dbPassword = builder.Password;
}
else
{
dbLogin = "******" + id;
dbPassword = this.Deployment.GetWindowsPassword();
// This happens always, wether or not we have windows auth.
this.Logger.LogInfo(true, "Adding SQL Login user '{0}' to database", dbLogin);
smo.Login login = utils.EnsureLoginSql(connection, dbLogin, dbPassword, true);
utils.BindUser(database, login, true);
}
this.Deployment.SetRuntimeSetting(keylogin, dbLogin);
this.Deployment.SetRuntimeSetting(keypassword, dbPassword);
// Create the database login, although we support windows auth,
// the recommendation is to use SQL AUTH for portability reasons
if (sqlServer.useWindowsAuth)
{
string sqlWindowsUserName = this.Deployment.WindowsUsernameFqdn(true);
this.Logger.LogInfo(true, "Adding Windows Login user '{0}' to database", sqlWindowsUserName);
// Depending on the setup this might fail, i.e. we are using a non-domain setup for chef (so the
// application users are local and the server is in a domain).
try
{
smo.Login loginw = utils.EnsureLoginWindows(connection, sqlWindowsUserName, true);
utils.BindUser(database, loginw, true);
}
catch (Exception e)
{
// 15401: "the domain controller for the domain where the login resides (the same or a different domain) is not available for some reason"
if ((e.InnerException?.InnerException as SqlException)?.Number != 15401)
{
throw;
}
this.Logger.LogError("Cannot add Windows login '{0}' to MSSQL Server '{1}'. This can happen if MSSQL and the local machine do not reside in the same domain.", sqlWindowsUserName, sqlServer.connectionString);
}
}
this.Deployment.SetRuntimeSetting($"services.{sqlSettings.id}.host", builder.DataSource);
// Build a connection string that the end user can handle
SqlConnectionStringBuilder clientBuilder = new SqlConnectionStringBuilder();
clientBuilder.UserID = dbLogin;
clientBuilder.Password = dbPassword;
clientBuilder.DataSource = builder.DataSource;
clientBuilder.InitialCatalog = databaseName;
this.Deployment.SetRuntimeSetting($"services.{sqlSettings.id}.connectionString", clientBuilder.ConnectionString);
string preferredConnectionString = clientBuilder.ConnectionString;
if (sqlServer.useWindowsAuth)
{
// Alternative connection string - integrated
SqlConnectionStringBuilder clientBuilderWindowsAuth = new SqlConnectionStringBuilder();
clientBuilderWindowsAuth.DataSource = builder.DataSource;
clientBuilderWindowsAuth.IntegratedSecurity = true;
clientBuilderWindowsAuth.InitialCatalog = databaseName;
this.Deployment.SetRuntimeSetting($"services.{sqlSettings.id}.connectionStringWindowsAuth", clientBuilderWindowsAuth.ConnectionString);
preferredConnectionString = clientBuilderWindowsAuth.ConnectionString;
}
this.Deployment.SetRuntimeSetting($"services.{sqlSettings.id}.connectionStringPreferred", preferredConnectionString);
if (!string.IsNullOrWhiteSpace(sqlSettings.customScript))
{
using (var clientConnection = new SqlConnection(preferredConnectionString))
{
clientConnection.Open();
var clientCommand = new SqlCommand(sqlSettings.customScript, clientConnection);
clientCommand.ExecuteNonQuery();
}
}
}