/// <summary>
/// Initializes a new instance of the Login class.
/// </summary>
public Login(SMO.Login login)
{
//AddinHelper.Common.WriteToDebugWindow(string.Format("SMOH.{0}({1})", "Login", login));
CreateDate = login.CreateDate.ToString("yyyy-MM-dd hh:mm:ss");
DefaultDatabase = login.DefaultDatabase;
Name = login.Name;
}
private static Microsoft.SqlServer.Management.Smo.Login ConstructLogin(LoginProperties loginProperties,
Server server)
{
var login = new Microsoft.SqlServer.Management.Smo.Login(server._server, loginProperties.Name)
{
LoginType = loginProperties.LoginType,
Sid = loginProperties.Sid,
DefaultDatabase = loginProperties.DefaultDatabase
};
if (loginProperties.LoginType == LoginType.SqlLogin)
{
if (loginProperties.PasswordHash != null)
{
login.Create(loginProperties.PasswordHash, LoginCreateOptions.IsHashed);
}
else if (loginProperties.Password != null)
{
login.Create(loginProperties.Password);
}
else
{
throw new ArgumentException("Password or hash was not supplied for sql login.");
}
}
else
{
login.Create();
}
return(login);
}
/// <summary>
/// Creates a new login on the server.
/// </summary>
/// <param name="loginProperties">Values for login initialization</param>
/// <param name="server">The server to create the login on</param>
public Login(LoginProperties loginProperties, Server server)
{
_server = server;
_login = _server.ConstructLogin(loginProperties.Name);
_login.LoginType = loginProperties.LoginType;
_login.Sid = loginProperties.Sid;
_login.DefaultDatabase = loginProperties.DefaultDatabase;
if (loginProperties.LoginType == Smo.LoginType.SqlLogin)
{
if (loginProperties.PasswordHash != null)
{
_login.Create(loginProperties.PasswordHash, Smo.LoginCreateOptions.IsHashed);
}
else if (loginProperties.Password != null)
{
_login.Create(loginProperties.Password);
}
else
{
throw new ArgumentException("Password or hash was not supplied for sql login.");
}
}
else
{
_login.Create();
}
}
//public static async Task<bool> CreateSQLMonitorLogin(string instanceName, out string message)
//{
// bool result = false;
// try
// {
// MSMO.Server server = SMO.Server.GetFromSMO(instanceName);
// MSMO.Login newLogin = new MSMO.Login(server, Data.Config.SQLInformationAgent_NTLoginName);
// newLogin.LoginType = MSMO.LoginType.WindowsUser;
// //newLogin.DefaultDatabase = "master";
// newLogin.Create();
// newLogin.AddToRole(Data.Config.SQLInformationAgent_ServerRole);
// message = "Success";
// result = true;
// //server.Logins.Add(newLogin);
// }
// catch (Microsoft.SqlServer.Management.Common.ConnectionFailureException ex)
// {
// VNC.AppLog.Warning(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 140);
// message = "Connection Failure";
// }
// catch (Exception ex)
// {
// VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 141);
// message = ex.Message;
// }
// return result;
//}
public static bool CreateSQLMonitorLogin(string instanceName, out string message)
{
bool result = false;
try
{
MSMO.Server server = SMO.Server.GetFromSMO(instanceName);
MSMO.Login newLogin = new MSMO.Login(server, Data.Config.SQLInformationAgent_NTLoginName);
newLogin.LoginType = MSMO.LoginType.WindowsUser;
//newLogin.DefaultDatabase = "master";
newLogin.Create();
newLogin.AddToRole(Data.Config.SQLInformationAgent_ServerRole);
message = "Success";
result = true;
//server.Logins.Add(newLogin);
}
catch (Microsoft.SqlServer.Management.Common.ConnectionFailureException ex)
{
VNC.AppLog.Warning(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 140);
message = "Connection Failure";
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 141);
message = ex.Message;
}
return(result);
}
public static void UpdateDataSet(this MSMO.Login login, Data.ApplicationDataSet.LoginsRow dataRow)
{
try
{
//dataRow.X = login.X;
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 7);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
}
}
public static void UpdateDataSet(this MSMO.Login login, Data.ApplicationDataSet.LoginsRow loginRow)
{
try
{
// Determine what might change and update it
}
catch (Exception ex)
{
// TODO(crhodes): Need to wrap anything above that throws an exception
// that we want to ignore, e.g. property not available because of
// SQL Edition.
PLLog.Error(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
throw ex;
}
}
private static void Update(MSMO.Login login, SQLInformation.Data.ApplicationDataSet.LoginsRow dataRow)
{
try
{
login.UpdateDataSet(dataRow);
UpdateDatabaseWithSnapShot(dataRow, "");
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 6);
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
}
private static SQLInformation.Data.ApplicationDataSet.LoginsRow Add(Guid instanceID, MSMO.Login login, string instanceName)
{
SQLInformation.Data.ApplicationDataSet.LoginsRow dataRow = null;
try
{
dataRow = Common.ApplicationDataSet.Logins.NewLoginsRow();
dataRow.ID = Guid.NewGuid();
dataRow.Name_Login = login.Name;
dataRow.Instance_ID = instanceID;
dataRow.Name_Instance = instanceName;
dataRow.CreateDate = login.CreateDate;
dataRow.DateLastModified = login.DateLastModified;
dataRow.DefaultDatabase = login.DefaultDatabase;
dataRow.LoginType = login.LoginType.ToString();
dataRow.SnapShotDate = DateTime.Now;
dataRow.SnapShotError = "";
Common.ApplicationDataSet.Logins.AddLoginsRow(dataRow);
Common.ApplicationDataSet.LoginsTA.Update(Common.ApplicationDataSet.Logins);
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 5);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
return(dataRow);
}
private static SQLInformation.Data.ApplicationDataSet.LoginsRow GetInfoFromSMO(Guid instanceID, MSMO.Login login, string instanceName)
{
#if TRACE
long startTicks = VNC.AppLog.Trace4("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
#endif
SQLInformation.Data.ApplicationDataSet.LoginsRow dataRow = null;
try
{
var dbs = from tb in Common.ApplicationDataSet.Logins
where tb.Instance_ID == instanceID
select tb;
var dbs2 = from db2 in dbs
where db2.Name_Login == login.Name
select db2;
if (dbs2.Count() > 0)
{
dataRow = dbs2.First();
Update(login, dataRow);
}
else
{
dataRow = Add(instanceID, login, instanceName);
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 3);
}
#if TRACE
VNC.AppLog.Trace4("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 4, startTicks);
#endif
return(dataRow);
}
/// <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();
}
}
}
/// <summary>
/// Creates a new login on the server.
/// </summary>
/// <param name="loginProperties">Values for login initialization</param>
/// <param name="server">The server to create the login on</param>
public Login(LoginProperties loginProperties, Server server)
{
_server = server;
_login = ConstructLogin(loginProperties, server);
}
public Login(Microsoft.SqlServer.Management.Smo.Login login, Server server)
{
_login = login;
_server = server;
}
public Login(Smo.Login login, Server server)
{
_login = login;
_server = server;
}