public static bool Save()
{
try
{
ProvisionedService.SaveFile();
return(true);
}
catch (Exception ex)
{
Logger.Error(Strings.ProvisionedServiceListSaveErrorLogMessage, ex.ToString());
return(false);
}
}
private void DeleteDatabase(ProvisionedService provisionedService)
{
string name = provisionedService.Name;
string user = provisionedService.User;
try
{
this.DeleteDatabaseUser(user);
Logger.Info(Strings.SqlNodeDeletingDatabaseInfoMessage, name);
using (TransactionScope ts = new TransactionScope())
{
using (SqlCommand takeOfflineCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, Strings.SqlNodeTakeDBOfflineSQL, name), this.connection))
{
takeOfflineCommand.ExecuteNonQuery();
}
using (SqlCommand bringOnlineCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, Strings.SqlNodeBringDBOnlineSQL, name), this.connection))
{
bringOnlineCommand.ExecuteNonQuery();
}
using (SqlCommand dropDatabaseCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, Strings.SqlNodeDropDatabaseSQL, name), this.connection))
{
dropDatabaseCommand.ExecuteNonQuery();
}
ts.Complete();
}
}
catch (Exception ex)
{
Logger.Fatal(Strings.SqlNodeCannotDeleteDBFatalMessage, ex.ToString());
}
}
private string GetInstanceHealthz(ProvisionedService instance)
{
string res = "ok";
try
{
string instanceConnectionString = string.Format(
CultureInfo.InvariantCulture,
Strings.SqlNodeConnectionString,
this.mssqlConfig.Host,
this.mssqlConfig.Port,
instance.User,
instance.Password);
using (SqlConnection databaseConnection = new SqlConnection(instanceConnectionString))
{
databaseConnection.Open();
databaseConnection.ChangeDatabase(instance.Name);
using (SqlCommand readDatabases = new SqlCommand("SELECT name from sys.tables", this.connection))
{
using (SqlDataReader reader = readDatabases.ExecuteReader())
{
reader.Read();
}
}
}
}
catch (Exception ex)
{
Logger.Warning(Strings.ErrorGettingDBTablesWarningMessage, instance.Name, ex.ToString());
res = "fail";
}
return res;
}
/// <summary>
/// Provisions an MS Sql Server database.
/// </summary>
/// <param name="planRequest">The payment plan for the service.</param>
/// <param name="credentials">Existing credentials for the service.</param>
/// <returns>
/// Credentials for the provisioned service.
/// </returns>
protected override ServiceCredentials Provision(ProvisionedServicePlanType planRequest, ServiceCredentials credentials)
{
//// todo: chek for plan
ProvisionedService provisioned_service = new ProvisionedService();
if (credentials == null)
{
throw new ArgumentNullException("credentials");
}
try
{
string name = credentials.Name;
string user = credentials.User;
string password = credentials.Password;
provisioned_service.Name = name;
provisioned_service.User = user;
provisioned_service.Password = password;
provisioned_service.Plan = planRequest;
this.CreateDatabase(provisioned_service);
if (!ProvisionedService.Save())
{
Logger.Error(Strings.SqlNodeCannotSaveProvisionedServicesErrorMessage, provisioned_service.SerializeToJson());
throw new MSSqlErrorException(MSSqlErrorException.MSSqlLocalDBError);
}
ServiceCredentials response = this.GenerateCredential(provisioned_service.Name, provisioned_service.User, provisioned_service.Password);
this.provisionServed += 1;
return response;
}
catch (Exception)
{
this.DeleteDatabase(provisioned_service);
throw;
}
}
private void CreateDatabase(ProvisionedService provisionedService)
{
string databaseName = provisionedService.Name;
string databasePassword = provisionedService.Password;
string databaseUser = provisionedService.User;
try
{
DateTime start = DateTime.Now;
Logger.Debug(Strings.SqlNodeCreateDatabaseDebugMessage, provisionedService.SerializeToJson());
this.createDBScript = this.ExtractSqlScriptFromTemplate(databaseName);
// split script on GO command
IEnumerable<string> commandStrings = Regex.Split(this.createDBScript, "^\\s*GO\\s*$", RegexOptions.Multiline);
using (TransactionScope ts = new TransactionScope())
{
foreach (string commandString in commandStrings)
{
if (!string.IsNullOrEmpty(commandString.Trim()) && !commandString.Contains("[master]"))
{
using (SqlCommand cmd = new SqlCommand(commandString, this.connection))
{
if (commandString.Contains("CREATE DATABASE"))
{
cmd.CommandTimeout = 0;
}
cmd.ExecuteNonQuery();
}
}
}
}
this.CreateDatabaseUser(databaseName, databaseUser, databasePassword);
long storage = this.StorageForService(provisionedService);
if (this.availableStorageBytes < storage)
{
throw new MSSqlErrorException(MSSqlErrorException.MSSqlDiskFull);
}
this.availableStorageBytes -= storage;
Logger.Debug(Strings.SqlNodeDoneCreatingDBDebugMessage, provisionedService.SerializeToJson(), (start - DateTime.Now).TotalSeconds);
}
catch (Exception ex)
{
Logger.Warning(Strings.SqlNodeCouldNotCreateDBWarningMessage, ex.ToString());
throw;
}
}
private object GetVarz(ProvisionedService instance)
{
var varz = new Dictionary<string, object>();
varz["name"] = instance.Name;
varz["port"] = instance.Port;
varz["plan"] = instance.Plan;
var usage = new Dictionary<string, object>();
varz["usage"] = usage;
usage["max_storage_size"] = this.maxStorageSizeMB * 1024 * 1024;
if (this.useFsrm)
{
try
{
usage["used_storage_size"] = this.dirAccounting.GetDirectorySize(this.GetInstanceDirectory(instance.Name));
}
catch (Exception ex)
{
Logger.Warning("Error getting FSRM info for {0}. Exception: {1}", instance.Name, ex.ToString());
usage["used_storage_size"] = -1;
}
}
else
{
usage["used_storage_size"] = -1;
}
// TODO: stefi: complete this instance varz
// add: samba active connectoins, ftp active connections, disk IO (if possible), network IO (if possible)
return varz;
}
/// <summary>
/// Returns storage capacity based on billing plan.
/// </summary>
/// <param name="provisionedService">The provisioned service.</param>
/// <returns>The storage quota in bytes.</returns>
private long StorageForService(ProvisionedService provisionedService)
{
switch (provisionedService.Plan)
{
case ProvisionedServicePlanType.Free: return this.maxDbSize;
default: throw new MSSqlErrorException(MSSqlErrorException.MSSqlInvalidPlan, provisionedService.Plan.ToString());
}
}
private void CreateInstanceStorage(ProvisionedService provisionedService)
{
string name = provisionedService.Name;
try
{
DateTime start = DateTime.Now;
Logger.Debug(Strings.SqlNodeCreateDatabaseDebugMessage, provisionedService.SerializeToJson());
if (this.useVhd)
{
string vhdDirectory = Path.Combine(this.baseDir, name);
string vhd = Path.Combine(this.baseDir, name + ".vhd");
VHDUtilities.CreateVHD(vhd, this.maxStorageSizeMB, this.vhdFixedSize);
VHDUtilities.MountVHD(vhd, vhdDirectory);
// TODO: stefi: revoke all permissions on parent direcotry if using VHD,
// to deny read/write permissions when VHD is not mounted or got dismounted
}
string directory = this.GetInstanceDirectory(name);
Directory.CreateDirectory(directory);
Logger.Debug(Strings.SqlNodeDoneCreatingDBDebugMessage, provisionedService.SerializeToJson(), (start - DateTime.Now).TotalSeconds);
}
catch (Exception ex)
{
Logger.Error("Cloud not create instance storage {0}. Exception {1}.", provisionedService.SerializeToJson(), ex.ToString());
throw;
}
}
private string GetStatus(ProvisionedService instance)
{
// TODO: migrate this into varz: ship service per-instance healthz data over varz: https://github.com/cloudfoundry/vcap-services/commit/8b12af491edfea58953cd07e1c80954a9006b22d
string res = "ok";
try
{
using (new UserImpersonator(instance.User, ".", instance.Password, false))
{
string testFile = Path.Combine(this.GetInstanceDirectory(instance.Name), Guid.NewGuid().ToString("N"));
File.WriteAllText(testFile, "test");
File.Delete(testFile);
}
}
catch (Exception ex)
{
Logger.Warning(Strings.ErrorGettingDBTablesWarningMessage, instance.Name, ex.ToString());
res = "fail";
}
return res;
}
/// <summary>
/// Bind the service instance.
/// </summary>
/// <param name="service">The service instance.</param>
/// <param name="binding">Binding information.</param>
private static void Bind(ProvisionedService service, ServiceBinding binding)
{
if (!WindowsUsersAndGroups.ExistsUser(binding.User))
{
CreateInstanceUser(service.Name, binding.User, binding.Password);
AddInstanceUserToGroup(service.Name, binding.User);
}
}
/// <summary>
/// Provisions an directory.
/// </summary>
/// <param name="planRequest">The payment plan for the service.</param>
/// <param name="credentials">Existing credentials for the service.</param>
/// <param name="version">The service version.</param>
/// <returns>
/// Credentials for the provisioned service.
/// </returns>
protected override ServiceCredentials Provision(string planRequest, ServiceCredentials credentials, string version)
{
if (planRequest != this.plan)
{
throw new FileServiceErrorException(FileServiceErrorException.FileServiceInvalidPlan);
}
if (!this.supportedVersions.Contains(version))
{
throw new FileServiceErrorException(ServiceException.UnsupportedVersion);
}
ProvisionedService provisioned_service = new ProvisionedService();
if (credentials == null)
{
credentials = this.GenerateCredentials();
}
try
{
string name = credentials.Name;
string user = credentials.User;
string password = credentials.Password;
int port = credentials.Port;
provisioned_service.Name = name;
provisioned_service.User = user;
provisioned_service.Password = password;
provisioned_service.Plan = planRequest;
provisioned_service.Port = port;
this.CreateInstanceStorage(provisioned_service);
this.InstanceSystemSetup(provisioned_service);
if (!ProvisionedService.Save())
{
Logger.Error(Strings.SqlNodeCannotSaveProvisionedServicesErrorMessage, provisioned_service.SerializeToJson());
throw new FileServiceErrorException(FileServiceErrorException.FileServiceLocalDBError);
}
ServiceCredentials response = this.GenerateCredential(provisioned_service.Name, provisioned_service.User, provisioned_service.Password, provisioned_service.Port.Value);
this.provisionServed += 1;
return response;
}
catch (Exception)
{
this.InstanceCleanup(provisioned_service);
throw;
}
}
private void InstanceSystemSetup(ProvisionedService provisionedService)
{
string name = provisionedService.Name;
string user = provisionedService.User;
string password = provisionedService.Password;
string directory = this.GetInstanceDirectory(name);
int port = provisionedService.Port.Value;
try
{
DateTime start = DateTime.Now;
Logger.Debug(Strings.SqlNodeCreateDatabaseDebugMessage, provisionedService.SerializeToJson());
// The group and users have to be recreated if the box was recreated by bosh.
// In that case only the file system resrouces remain. Every system
// resource of configuration has to be provisioned again.
//
// Create the group and user if necessary
if (!WindowsUsersAndGroups.ExistsGroup(name))
{
Logger.Info("Creating window group: {0}, for instance {1}", name, name);
CreateInstanceGroup(name);
// Add group permissions to directory
// TODO: stefi: consider cleaning up orphan users and groups
Logger.Info("Adding group permissions for directory: {0}", directory);
AddDirectoryPermissions(directory, name);
if (!WindowsUsersAndGroups.ExistsUser(user))
{
Logger.Info("Creating user: {0}, for instance {1}", user, name);
CreateInstanceUser(name, user, password);
AddInstanceUserToGroup(name, user);
}
}
// create the vhd if necessary
if (this.useVhd)
{
string vhdDirectory = Path.Combine(this.baseDir, name);
string vhd = Path.Combine(this.baseDir, name + ".vhd");
if (!VHDUtilities.IsMountPointPresent(vhdDirectory))
{
Logger.Info("Mounting VHD: {0}, at: {1}, for instance {2}", vhd, vhdDirectory, name);
VHDUtilities.MountVHD(vhd, vhdDirectory);
}
}
if (this.useFsrm)
{
Logger.Info("Setting up windows FSRM for instance: {0}, with quota size: {1} MB", name, this.maxStorageSizeMB);
this.dirAccounting.SetDirectoryQuota(directory, this.maxStorageSizeMB * 1024 * 1024);
}
// create ftp service if necessary
if (!FtpUtilities.Exists(name))
{
Logger.Info("Creating ftp site for instance: {0}, at: {1}, with port: {2}", name, directory, port);
FtpUtilities.CreateFtpSite(name, directory, port);
}
if (!FtpUtilities.HasGroupAccess(name, name))
{
// Add group permissions to ftp share
Logger.Info("Adding group permission for ftp site for instance: {0}", name);
FtpUtilities.AddGroupAccess(name, name);
}
// create the windows share with necessary permission
var ws = new WindowsShare(name);
if (!ws.Exists())
{
Logger.Info("Creating windows share for instance: {0}, at: {1}", name, directory);
ws = WindowsShare.CreateShare(name, directory);
}
if (!ws.HasPermission(name))
{
// Add group permissions to windows share
Logger.Info("Adding group permission for windows share for instance: {0}", name);
ws.AddSharePermission(name);
}
Logger.Debug("Done setting up instance {0}. Took {1}s.", provisionedService.SerializeToJson(), (start - DateTime.Now).TotalSeconds);
}
catch (Exception ex)
{
Logger.Error("Cloud not setup instance {0}. Exception {1}.", provisionedService.SerializeToJson(), ex.ToString());
}
}
private bool InstanceCleanup(ProvisionedService provisionedService)
{
bool success = true;
string name = provisionedService.Name;
try
{
Logger.Info(Strings.SqlNodeDeletingDatabaseInfoMessage, name);
// Try delete ftp site.
try
{
FtpUtilities.DeleteFtpSite(name);
}
catch (Exception ex)
{
success = false;
Logger.Error("Unable to delete FTP site for instance {0}. Exception: {1}", name, ex.ToString());
}
// Try delete windows share.
try
{
WindowsShare ws = new WindowsShare(name);
ws.DeleteShare();
}
catch (Exception ex)
{
success = false;
Logger.Error("Unable to delete Windows Share for instance {0}. Exception: {1}", name, ex.ToString());
}
// Try delete directory quota.
try
{
string directory = this.GetInstanceDirectory(name);
if (this.useFsrm)
{
this.dirAccounting.RemoveDirectoryQuota(directory);
}
}
catch (Exception ex)
{
success = false;
Logger.Error("Unable to delete directory quota rule for instance {0}. Exception: {1}", name, ex.ToString());
}
if (!this.useVhd)
{
// Try delete instance directory
try
{
string directory = this.GetInstanceDirectory(name);
Directory.Delete(directory, true);
}
catch (Exception ex)
{
success = false;
Logger.Error("Unable to delete instance directory for instance {0}. Exception: {1}", name, ex.ToString());
}
}
if (this.useVhd)
{
string vhdDirectory = Path.Combine(this.baseDir, name);
string vhd = Path.Combine(this.baseDir, name + ".vhd");
// Try unmount VHD
try
{
VHDUtilities.UnmountVHD(vhd);
}
catch (Exception ex)
{
success = false;
Logger.Error("Unable to un-mount VHD {0} for instance {1}. Exception: {2}", vhd, name, ex.ToString());
}
// Try delete VHD
try
{
File.Delete(vhd);
}
catch (Exception ex)
{
success = false;
Logger.Error("Unable to delete VHD file {0} for instance {1}. Exception: {2}", vhd, name, ex.ToString());
}
// Try delete mount dir
try
{
// Do not use recursive. The directory should be empty.
// If the dir contains data, some users data may be lost
Directory.Delete(vhdDirectory, false);
}
catch (Exception ex)
{
success = false;
Logger.Error("Unable to delete VHD file {0} for instance {1}. Exception: {2}", vhd, name, ex.ToString());
}
}
// Try delete the user
try
{
DeleteInstanceUser(provisionedService.User);
}
catch (Exception ex)
{
success = false;
Logger.Error("Unable to delete user {0} for instance {1}. Exception: {2}", provisionedService.User, name, ex.ToString());
}
// Try delete the group
try
{
DeleteInstanceGroup(name);
}
catch (Exception ex)
{
success = false;
Logger.Error("Unable to delete group {0} for instance {1}. Exception: {2}", name, name, ex.ToString());
}
}
catch (Exception ex)
{
success = false;
Logger.Fatal(Strings.SqlNodeCannotDeleteDBFatalMessage, ex.ToString());
}
return success;
}
public void CreateDatabaseTest()
{
try
{
Node_Accessor target = new Node_Accessor();
target.mssqlConfig = new MSSqlOptions();
UhuruSection config = UhuruSection.GetSection();
target.mssqlConfig.Host = config.Service.MSSql.Host;
target.mssqlConfig.User = config.Service.MSSql.User;
target.mssqlConfig.Password = config.Service.MSSql.Password;
target.mssqlConfig.Port = config.Service.MSSql.Port;
target.maxLongQuery = config.Service.MaxLengthyQuery;
target.mssqlConfig.LogicalStorageUnits = config.Service.MSSql.LogicalStorageUnits;
target.mssqlConfig.InitialDataSize = config.Service.MSSql.InitialDataSize;
target.mssqlConfig.InitialLogSize = config.Service.MSSql.InitialLogSize;
target.mssqlConfig.MaxDataSize = config.Service.MSSql.MaxDataSize;
target.mssqlConfig.MaxLogSize = config.Service.MSSql.MaxLogSize;
target.mssqlConfig.DataFileGrowth = config.Service.MSSql.DataFileGrowth;
target.mssqlConfig.LogFileGrowth = config.Service.MSSql.LogFileGrowth;
target.connection = target.ConnectMSSql();
ProvisionedService provisionedService = new ProvisionedService();
DateTime now = DateTime.Now;
string decoration = string.Format(
"{0}_{1}_{2}",
now.Hour,
now.Minute,
now.Second);
provisionedService.Name = "CreateDatabaseTest_" + decoration;
provisionedService.User = "******" + decoration;
provisionedService.Password = "******";
provisionedService.Plan = ProvisionedServicePlanType.Free;
//////////////////////////////////////////////////////////////////////////
// create the provisioned service db and user
//////////////////////////////////////////////////////////////////////////
target.CreateDatabase(provisionedService);
Thread.Sleep(500);
//////////////////////////////////////////////////////////////////////////
// assert the existence of the db files
//////////////////////////////////////////////////////////////////////////
string dbScript = target.createDBScript;
Regex fnRegex = new Regex(@"FILENAME = N'(.*)'");
MatchCollection matches = fnRegex.Matches(dbScript);
foreach (Match m in matches)
{
string fileName = m.Value.Substring(m.Value.IndexOf('\'')).Trim(new char[] { '\'' });
Assert.IsTrue(File.Exists(fileName), string.Format("File '{0}' does not exist", fileName));
}
//////////////////////////////////////////////////////////////////////////
// try to connect as the newly created user
//////////////////////////////////////////////////////////////////////////
string sqlTestConnString = string.Format(
CultureInfo.InvariantCulture,
"Data Source={0},{1};User Id={2};Password={3};MultipleActiveResultSets=true;Pooling=false",
target.mssqlConfig.Host,
target.mssqlConfig.Port,
provisionedService.User,
provisionedService.Password);
SqlConnection sqlTest = new SqlConnection(sqlTestConnString);
sqlTest.Open();
//////////////////////////////////////////////////////////////////////////
// try to connect create a table as the newly created user
//////////////////////////////////////////////////////////////////////////
SqlCommand cmdTest = new SqlCommand(
string.Format("CREATE TABLE [{0}].[dbo].[TestTable]([Command] [varchar](100) NULL, [Description] [varchar](50) NULL) ON [DATA]", provisionedService.Name),
sqlTest);
cmdTest.ExecuteNonQuery();
sqlTest.Close();
//////////////////////////////////////////////////////////////////////////
// try to operate on the service db as a different user
//////////////////////////////////////////////////////////////////////////
// connect as sa
sqlTest.ConnectionString = string.Format(
CultureInfo.InvariantCulture,
"Data Source={0},{1};User Id={2};Password={3};MultipleActiveResultSets=true;Pooling=false",
target.mssqlConfig.Host,
target.mssqlConfig.Port,
target.mssqlConfig.User,
target.mssqlConfig.Password);
sqlTest.Open();
string dummyUser = "******" + decoration;
string dummyPwd = "password1234!";
//create a dummy user
string createLoginString = string.Format(@"CREATE LOGIN {0} WITH PASSWORD = '******'", dummyUser, dummyPwd);
cmdTest = new SqlCommand(createLoginString, sqlTest);
cmdTest.ExecuteNonQuery();
sqlTest.Close();
// connect as the dummy user
sqlTest.ConnectionString = string.Format(
CultureInfo.InvariantCulture,
"Data Source={0},{1};User Id={2};Password={3};MultipleActiveResultSets=true;Pooling=false",
target.mssqlConfig.Host,
target.mssqlConfig.Port,
dummyUser,
dummyPwd);
sqlTest.Open();
// try to drop the service db
try
{
cmdTest.CommandText = string.Format("CREATE TABLE [{0}].[dbo].[DummyTable]([Command] [varchar](100) NULL, [Description] [varchar](50) NULL) ON [DATA]", provisionedService.Name);
cmdTest.ExecuteNonQuery();
Assert.Fail("Other users have read/write access to the service db");
}
catch (SqlException sex)
{
}
//////////////////////////////////////////////////////////////////////////
//Remove database
//////////////////////////////////////////////////////////////////////////
ServiceCredentials sc = new ServiceCredentials();
sc.UserName = provisionedService.User;
sc.Password = provisionedService.Password;
sc.Name = provisionedService.Name;
sc.User = provisionedService.User;
target.DeleteDatabase(provisionedService);
//target.Unprovision(provisionedService.Name, new ServiceCredentials[] { sc });
sqlTest.Close();
target.connection.Close();
}
catch (System.Exception ex)
{
Assert.Fail(ex.Message);
}
}
