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(); } } }