private void LoadAccounts()
{
// Load the listbox with the list of existing server logins, filtering
// out the SA, Windows, or server role accounts as well as any built-in
// accounts that begin with "##".
SqlConnectionInfo conInfo;
SqlContext ctx;
SqlCommand cmd;
DataSet ds;
DataTable dt;
WaitForm waitForm;
string login;
wizard.Enabled = false;
this.Update();
waitForm = new WaitForm("Scanning accounts...");
waitForm.TopLevel = true;
waitForm.Show();
waitForm.Update();
Thread.Sleep(2000);
// Connect to the master database.
conInfo = new SqlConnectionInfo(conString);
conInfo.Database = "master";
ctx = new SqlContext(conInfo.ToString());
try
{
ctx.Open();
// Get the accounts (note that the sp_helplogins sproc does not exist on SQL Azure).
if (ctx.IsSqlAzure)
{
cmd = ctx.CreateCommand("select name as 'LoginName' from sys.sql_logins");
}
else
{
cmd = ctx.CreateSPCall("sp_helplogins");
}
ds = ctx.ExecuteSet(cmd);
dt = ds.Tables["0"];
accountList.Items.Clear();
foreach (DataRow row in dt.Rows)
{
login = SqlHelper.AsString(row["LoginName"]);
// Append the account, skipping any that are empty or
// appear to be a server role, a Windows domain account,
// or a built-in account.
if (login == null)
{
continue; // Empty
}
if (login.IndexOf('\\') != -1)
{
continue; // Windows account
}
if (String.Compare(login, "sa", true) == 0)
{
continue; // SA account
}
if (login.StartsWith("##"))
{
continue; // Built-in account
}
accountList.Items.Add(login);
if (String.Compare(login, (string)wizard.SetupState["account"], true) == 0)
{
accountList.SelectedIndex = accountList.Items.Count - 1;
}
}
}
catch (Exception e)
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show("Cannot load database accounts.\r\n\r\n" + e.Message,
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
finally
{
ctx.Close();
}
wizard.Enabled = true;
waitForm.Close();
}
private void LoadDatabases()
{
// Load the listbox with the list of databases on the server and
// while we're at it we'll extract the master database's data
// and log file folders.
//
// Note that we're not going to add the following system databases
// to the list:
//
// master
// model
// msdb
// tempdb
// ReportServer
// ReportServerTempDB
Dictionary <string, bool> ignoreDBs = new Dictionary <string, bool>(StringComparer.OrdinalIgnoreCase);
SqlContext ctx;
SqlCommand cmd;
DataSet ds;
DataTable dt;
WaitForm waitForm;
string dbName;
ignoreDBs.Add("master", true);
ignoreDBs.Add("model", true);
ignoreDBs.Add("msdb", true);
ignoreDBs.Add("tempdb", true);
ignoreDBs.Add("ReportServer", true);
ignoreDBs.Add("ReportServerTempDB", true);
wizard.Enabled = false;
this.Update();
waitForm = new WaitForm("Scanning databases...");
waitForm.TopLevel = true;
waitForm.Show();
waitForm.Update();
Thread.Sleep(2000);
ctx = new SqlContext(conString);
try
{
ctx.Open();
// Get the databases (note that the sp_databases sproc does not
// exist on SQL Azure).
if (wizard.IsSqlAzure)
{
cmd = ctx.CreateCommand("select name as DATABASE_NAME from sys.sysdatabases");
}
else
{
cmd = ctx.CreateSPCall("sp_databases");
}
dt = ctx.ExecuteTable(cmd);
databaseList.Items.Clear();
foreach (DataRow row in dt.Rows)
{
dbName = SqlHelper.AsString(row["DATABASE_NAME"]);
if (ignoreDBs.ContainsKey(dbName))
{
continue;
}
databaseList.Items.Add(dbName);
if (String.Compare(dbName, (string)wizard.SetupState["database"], true) == 0)
{
databaseList.SelectedIndex = databaseList.Items.Count - 1;
}
}
if (!wizard.IsSqlAzure && (masterDataPath == null || masterLogPath == null))
{
// Get the master database file paths
cmd = ctx.CreateSPCall("sp_helpdb");
cmd.Parameters.Add("@dbname", SqlDbType.NVarChar).Value = "master";
ds = ctx.ExecuteSet(cmd);
dt = ds.Tables["1"];
foreach (DataRow row in dt.Rows)
{
string file;
int pos;
if (SqlHelper.AsString(row["usage"]).ToLowerInvariant().IndexOf("data") != -1)
{
file = SqlHelper.AsString(row["filename"]);
pos = file.LastIndexOf('\\');
if (pos != -1)
{
masterDataPath = file.Substring(0, pos + 1);
}
}
else if (SqlHelper.AsString(row["usage"]).ToLowerInvariant().IndexOf("log") != -1)
{
file = SqlHelper.AsString(row["filename"]);
pos = file.LastIndexOf('\\');
if (pos != -1)
{
masterLogPath = file.Substring(0, pos + 1);
}
}
}
// Set the paths to the empty string if all else fails
if (masterDataPath == null)
{
masterDataPath = string.Empty;
}
if (masterLogPath == null)
{
masterLogPath = string.Empty;
}
}
}
catch (Exception e)
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show("Setup could not connect to the database. Please check\r\nthe server name and account settings.\r\n\r\n" + e.Message,
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
finally
{
ctx.Close();
}
wizard.Enabled = true;
waitForm.Close();
}
/// <summary>
/// Called when the step is deactivated.
/// </summary>
/// <param name="steps">The step list.</param>
/// <param name="forward"><c>true</c> if we're stepping forward in the wizard.</param>
/// <returns><c>true</c> if the transition can proceed.</returns>
public bool OnStepOut(WizardStepList steps, bool forward)
{
if (!forward)
{
return(true);
}
string database;
database = (string)databaseList.SelectedItem;
if (database == null)
{
MessageBox.Show("Select a database from the list or click Create\r\nto create a new one.", wizard.SetupTitle,
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
else if (database.ToUpper() == "MASTER")
{
MessageBox.Show("Cannot install into the MASTER database.", wizard.SetupTitle,
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
// Take a look at the database and ensure that it is either empty
// or is already associated with this product ID and database type.
SqlContext ctx;
SqlCommand cmd;
DataTable dt;
WaitForm waitForm;
string cs;
wizard.Enabled = false;
this.Update();
waitForm = new WaitForm(string.Format("Examining database [{0}]...", database));
waitForm.TopLevel = true;
waitForm.Show();
waitForm.Update();
Thread.Sleep(2000);
cs = string.Format("server={0};database={1};uid={2};pwd={3}",
wizard.SetupState["server"],
database,
wizard.SetupState["adminAccount"],
wizard.SetupState["adminPassword"]);
ctx = new SqlContext(cs);
try
{
ctx.Open();
// I'm going to determine whether the database is empty or
// not by looking at the sysobjects table. We'll consider
// it to be not empty if any these conditions are true:
//
// 1. Any user tables are present whose names
// don't begin with "dt".
//
// 2. Any stored procedures or functions are present
// whose names don't begin with "dt".
cmd = ctx.CreateCommand("select 1 from sysobjects where (xtype='U' or xtype='P' or xtype='FN') and name not like 'dt%'");
dt = ctx.ExecuteTable(cmd);
if (dt.Rows.Count == 0)
{
// The database appears to be empty.
wizard.SetupState["Action"] = "Install";
}
else
{
// The database appears to be not empty. Try calling the
// GetProductInfo procedure. If this fails then assume that
// the database belongs to some other application. If it
// succeeds then check the productID and database type against
// the setup settings.
try
{
cmd = ctx.CreateSPCall("GetProductInfo");
dt = ctx.ExecuteTable(cmd);
// Compare the database's product ID and database type to
// the setup settings.
if (SqlHelper.AsString(dt.Rows[0]["ProductID"]) != wizard.SetupState["productID"] ||
SqlHelper.AsString(dt.Rows[0]["DatabaseType"]) != wizard.SetupState["databaseType"])
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show(string.Format("Database [{0}] is configured for use by [{1}:{2}].\r\n\r\nPlease select a different database.",
database,
SqlHelper.AsString(dt.Rows[0]["ProductName"]),
SqlHelper.AsString(dt.Rows[0]["DatabaseType"])),
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
// The database looks like can accept the installation.
wizard.SetupState["Action"] = "Upgrade";
wizard.SetupState["CurSchemaVersion"] = SqlHelper.AsString(dt.Rows[0]["SchemaVersion"]);
}
catch
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show(string.Format("Database [{0}] is not empty and appears to in use by another application.\r\n\r\nPlease select a different database.", database),
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
}
catch (Exception e)
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show("Setup could not connect to the database. Please check\r\nthe server name and account settings.\r\n\r\n" + e.Message,
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
finally
{
ctx.Close();
}
wizard.Enabled = true;
waitForm.Close();
// Success!
wizard.SetupState["database"] = database;
wizard.SetupState["connectionString"] = string.Format("server={0};database={1};uid={2};pwd={3}",
wizard.SetupState["server"],
wizard.SetupState["database"],
wizard.SetupState["adminAccount"],
wizard.SetupState["adminPassword"]);
this.Hide();
return(true);
}
public void SentinelServiceDB_DeployDB()
{
SqlTestDatabase dbTest;
Package dbPackage = null;
DBPackageInstaller dbInstaller;
DBInstallParams dbParams;
DBInstallResult result;
using (dbTest = SqlTestDatabase.Create())
{
SqlConnectionInfo conInfo;
SqlContext ctx = null;
SqlCommand cmd;
DataTable dt;
try
{
// Deploy to a non-existent database
dbPackage = new Package(EnvironmentVars.Expand("$(LT_BUILD)\\LillTek.SentinelService.dbpack"));
dbParams = new DBInstallParams("SentinelService", dbTest.ConnectionInfo.Database);
dbInstaller = new DBPackageInstaller(dbPackage);
result = dbInstaller.Install(dbParams);
Assert.AreEqual(DBInstallResult.Installed, result);
conInfo = SqlConnectionInfo.Parse(dbInstaller.ConnectionString);
ctx = new SqlContext(conInfo);
ctx.Open();
cmd = ctx.CreateSPCall("GetProductInfo");
dt = ctx.ExecuteTable(cmd);
Assert.AreEqual(1, dt.Rows.Count);
cmd = ctx.CreateSPCall("Ping");
dt = ctx.ExecuteTable(cmd);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("OK", SqlHelper.AsString(dt.Rows[0]["STATUS"]));
ctx.Close();
ctx = null;
// Deploy again and we should see that the database is up-to-date.
SqlConnection.ClearAllPools();
result = dbInstaller.Install(dbParams);
Assert.AreEqual(DBInstallResult.UpToDate, result);
}
finally
{
if (dbPackage != null)
{
dbPackage.Close();
}
if (ctx != null)
{
ctx.Close();
}
}
}
}
/// <summary>
/// Creates the application database account if one doesn't already exist.
/// </summary>
private void CheckAppAccount()
{
List <string> accounts;
string login;
string cs;
SqlContext ctx;
SqlCommand cmd;
DataSet ds;
DataTable dt;
bool exists;
if (account == null)
{
return; // Looks like we're using integrated security
}
// Get the current set of accounts from the database
cs = string.Format("server={0};database={1};{2}", server, database, dbParams.AdminSecurity);
ctx = new SqlContext(cs);
try
{
ctx.Open();
// Get the accounts (note that the sp_helplogins sproc does not exist on SQL Azure).
if (ctx.IsSqlAzure)
{
cmd = ctx.CreateCommand("select name as 'LoginName' from sys.sql_logins");
}
else
{
cmd = ctx.CreateSPCall("sp_helplogins");
}
ds = ctx.ExecuteSet(cmd);
dt = ds.Tables["0"];
accounts = new List <string>();
foreach (DataRow row in dt.Rows)
{
login = SqlHelper.AsString(row["LoginName"]);
// Append the account, skipping any that are empty or
// appear to be a server role or a Windows domain account.
if (login != null && login.IndexOf('\\') == -1)
{
accounts.Add(login);
}
}
}
finally
{
ctx.Close();
}
// Create the account, recreating it if it already exists.
exists = false;
for (int i = 0; i < accounts.Count; i++)
{
if (String.Compare(account, accounts[i], true) == 0)
{
exists = true;
break;
}
}
cs = string.Format("server={0};database=master;{1}", server, database, dbParams.AdminSecurity);
ctx = new SqlContext(cs);
try
{
ctx.Open();
if (exists)
{
if (ctx.IsSqlAzure)
{
cmd = ctx.CreateCommand("drop login '{0}'", account);
}
else
{
cmd = ctx.CreateSPCall("sp_droplogin");
cmd.Parameters.Add("@loginame", SqlDbType.VarChar).Value = account;
}
ctx.Execute(cmd);
}
if (ctx.IsSqlAzure)
{
ctx.CreateCommand("create login {0} with password='******'", account, password);
}
else
{
cmd = ctx.CreateSPCall("sp_addlogin");
cmd.Parameters.Add("@loginame", SqlDbType.VarChar).Value = account;
cmd.Parameters.Add("@passwd", SqlDbType.VarChar).Value = password;
}
ctx.Execute(cmd);
}
finally
{
ctx.Close();
}
}
/// <summary>
/// Used during touch-free deployment to verify that the database doesn't exist
/// or is empty (in which case an installation needs to be performed) or if the
/// database does exist and belongs to the same product (so an upgrade should
/// be attempted).
/// </summary>
/// <returns>Indicates whether an install or upgrade should be performed.</returns>
private DBInstallResult CheckDatabase()
{
// Take a look at the database and ensure that it is either empty
// or is already associated with this product ID and database type.
DBInstallResult result = DBInstallResult.Unknown;
SqlContext ctx;
SqlCommand cmd;
DataTable dt;
string cs;
string dbName;
string query;
bool exists;
cs = string.Format("server={0};database={1};{2}", server, database, dbParams.AdminSecurity);
ctx = new SqlContext(cs);
try
{
ctx.Open();
// Create the database if the doesn't already exist.
cmd = ctx.CreateSPCall("sp_databases");
dt = ctx.ExecuteTable(cmd);
exists = false;
foreach (DataRow row in dt.Rows)
{
dbName = SqlHelper.AsString(row["DATABASE_NAME"]);
if (String.Compare(dbName, database, true) == 0)
{
exists = true;
break;
}
}
if (!exists)
{
if (dbParams.DBPath != null && dbParams.LogPath != null)
{
Helper.CreateFolderTree(dbParams.DBPath);
Helper.CreateFolderTree(dbParams.LogPath);
query = string.Format("create database [{0}] on (name='{0}_data', filename='{1}') log on (name='{0}_log', filename='{2}')", database, dbParams.DBPath, dbParams.LogPath);
}
else if (dbParams.DBPath != null)
{
Helper.CreateFolderTree(dbParams.DBPath);
query = string.Format("create database [{0}] on (name='{0}_data', filename='{1}')", database, dbParams.DBPath);
}
else
{
query = string.Format("create database [{0}]", database);
}
cmd = ctx.CreateCommand(query);
ctx.Execute(cmd);
return(DBInstallResult.Installed);
}
// I'm going to determine whether the database is empty or
// not by looking at the sysobjects table. We'll consider
// it to be not empty if any these conditions are true:
//
// 1. Any user tables are present whose names
// don't begin with "dt".
//
// 2. Any stored procedures or functions are present
// whose names don't begin with "dt".
cmd = ctx.CreateCommand("select 1 from sysobjects where (xtype='U' or xtype='P' or xtype='FN') and name not like 'dt%'");
dt = ctx.ExecuteTable(cmd);
if (dt.Rows.Count == 0)
{
// The database appears to be empty.
result = DBInstallResult.Installed;
}
else
{
// The database appears to be not empty. Try calling the
// GetProductInfo procedure. If this fails then assume that
// the database belongs to some other application. If it
// succeeds then check the productID and database type against
// the setup settings.
try
{
cmd = ctx.CreateSPCall("GetProductInfo");
dt = ctx.ExecuteTable(cmd);
// Compare the database's product ID and database type to
// the setup settings.
if (SqlHelper.AsString(dt.Rows[0]["ProductID"]) != productID ||
SqlHelper.AsString(dt.Rows[0]["DatabaseType"]) != databaseType)
{
throw new InvalidOperationException(string.Format("Package cannot be deployed. Database [{0}] is configured for use by [{1}:{2}].",
database, SqlHelper.AsString(dt.Rows[0]["ProductName"]), SqlHelper.AsString(dt.Rows[0]["DatabaseType"])));
}
// The database looks like can accept the installation.
result = DBInstallResult.Upgraded;
curVersion = new Version(SqlHelper.AsString(dt.Rows[0]["SchemaVersion"]));
}
catch
{
throw new InvalidOperationException(string.Format("Database [{0}] is not empty and appears to in use by another application.\r\n\r\nPlease select a different database.", database));
}
}
}
finally
{
ctx.Close();
}
Assertion.Test(result != DBInstallResult.Unknown);
return(result);
}
private void okButton_Click(object sender, System.EventArgs args)
{
account = accountBox.Text.Trim();
if (account.Length == 0)
{
MessageBox.Show("Please enter an account name.",
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
accountBox.Focus();
accountBox.SelectAll();
return;
}
password = passwordBox.Text.Trim();
if (password != confirmBox.Text.Trim())
{
MessageBox.Show("The password and confirmation are not the same.",
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
passwordBox.Text = string.Empty;
confirmBox.Text = string.Empty;
passwordBox.Focus();
passwordBox.SelectAll();
return;
}
// Create the account
SqlConnectionInfo conInfo;
SqlContext ctx;
SqlCommand cmd;
WaitForm waitForm;
wizard.Enabled = false;
this.Update();
waitForm = new WaitForm("Creating account [" + account + "]...");
waitForm.TopLevel = true;
waitForm.Show();
waitForm.Update();
Thread.Sleep(2000);
conInfo = new SqlConnectionInfo((string)wizard.SetupState["connectionString"]);
conInfo.Database = "master";
ctx = new SqlContext(conInfo.ToString());
try
{
ctx.Open();
if (ctx.IsSqlAzure)
{
cmd = ctx.CreateCommand("create login {0} with password='******'", account, password);
}
else
{
cmd = ctx.CreateSPCall("sp_addlogin");
cmd.Parameters.Add("@loginame", SqlDbType.VarChar).Value = account;
cmd.Parameters.Add("@passwd", SqlDbType.VarChar).Value = password;
}
ctx.Execute(cmd);
}
catch (Exception e)
{
passwordBox.Text = string.Empty;
confirmBox.Text = string.Empty;
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show("Cannot create the new database account.\r\n\r\n" + e.Message,
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
finally
{
ctx.Close();
}
wizard.Enabled = true;
waitForm.Close();
// Success!
DialogResult = DialogResult.OK;
}
/// <summary>
/// Called when the step is deactivated.
/// </summary>
/// <param name="steps">The step list.</param>
/// <param name="forward"><c>true</c> if we're stepping forward in the wizard.</param>
/// <returns><c>true</c> if the transition can proceed.</returns>
public bool OnStepOut(WizardStepList steps, bool forward)
{
if (!forward)
{
return(true);
}
string server;
string account;
string password;
// Validate the dialog entries.
server = serverName.Text.Trim();
account = adminAccount.Text.Trim();
password = adminPassword.Text.Trim();
if (server == string.Empty)
{
MessageBox.Show("Please enter the database server name or IP address.", wizard.SetupTitle,
MessageBoxButtons.OK, MessageBoxIcon.Error);
serverName.Focus();
serverName.SelectAll();
return(false);
}
if (account == string.Empty)
{
MessageBox.Show("Please enter the database administrator account.", wizard.SetupTitle,
MessageBoxButtons.OK, MessageBoxIcon.Error);
adminAccount.Focus();
adminAccount.SelectAll();
return(false);
}
// Verify that the database server exists and that the account information
// is valid by connecting to the database executing sp_helpsrvrolemember
// and verifying that the account is returned as one of the accounts having
// the sysadmin role.
//
// Note that sp_helpsrvrolemember does not exist on SQL Azure, so we're going
// to first determine whether we're running on Azure and then skip this call
// if we are.
string conString;
SqlContext ctx;
SqlCommand cmd;
DataTable dt;
bool found;
WaitForm waitForm;
conString = string.Format("server={0};database={1};uid={2};pwd={3}",
server, "master", account, password);
wizard.Enabled = false;
this.Update();
waitForm = new WaitForm("Verifying administrator credentials...");
waitForm.TopLevel = true;
waitForm.Show();
waitForm.Update();
Thread.Sleep(2000);
ctx = new SqlContext(conString);
try
{
ctx.Open();
// SQL Azure detection
wizard.IsSqlAzure = ctx.IsSqlAzure;
// Verify that the account is an admin
if (!wizard.IsSqlAzure)
{
cmd = ctx.CreateSPCall("sp_helpsrvrolemember");
cmd.Parameters.Add("@srvrolename", SqlDbType.NVarChar).Value = "sysadmin";
dt = ctx.ExecuteTable(cmd);
found = false;
foreach (DataRow row in dt.Rows)
{
if (String.Compare(account, SqlHelper.AsString(row["MemberName"]), true) == 0)
{
found = true;
break;
}
}
if (!found)
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show(string.Format("Account [{0}] is not a system administrator.", account.ToUpper()),
wizard.SetupTitle,
MessageBoxButtons.OK, MessageBoxIcon.Error);
adminAccount.Focus();
adminAccount.SelectAll();
return(false);
}
}
}
catch (Exception e)
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show("Setup could not connect to the database. Please check\r\nthe server name and account settings.\r\n\r\n" + e.Message,
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
finally
{
ctx.Close();
}
wizard.Enabled = true;
waitForm.Close();
wizard.SetupState["connectionString"] = conString;
wizard.SetupState["server"] = server;
wizard.SetupState["adminAccount"] = account;
wizard.SetupState["adminPassword"] = password;
this.Hide();
return(true);
}
