int UpDateSsb()
{
updatedobj = null;
//if (!isDirty)
//return 0;
if (!ValidateData()) {
Cursor crs = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
try {
Database db = null;
ServiceBroker sb = null;
if (ssbType != SsbEnum.Database && ssbType != SsbEnum.Login && ssbType != SsbEnum.EndPoint) {
sb = dBase.ServiceBroker;
}
switch (ssbType) {
case SsbEnum.Database:
MasterKey mk = null;
SSBIDatabase sbd = null;
if (isEdit) {
sbd = (SSBIDatabase)objToUpdate;
db = sbd.DataBase;
}
else {
db = new Database();
db.Name = txtName.Text;
db.Parent = dbServ.SMOServer;
}
if (isEdit) {
if(db.MasterKey != null && db_chkMasterKey.Checked == false) {
mk = db.MasterKey;
mk.Drop();
}
else if (db.MasterKey == null && db_chkMasterKey.Checked) {
mk = new MasterKey();
mk.Parent = db;
mk.Create(db_txtMkPwd.Text);
}
db.Alter();
if (sbd.IsTrustworthy != db_chkTrustWorthy.Checked)
sbd.IsTrustworthy = db_chkTrustWorthy.Checked;
}
else {
db.Create();
sbd = new SSBIDatabase(db);
if (db_chkMasterKey.Checked) {
mk = new MasterKey();
mk.Parent = db;
mk.Create(db_txtMkPwd.Text);
}
if (db_chkTrustWorthy.Checked) {
sbd.IsTrustworthy = true;
}
}
if (dBase == null)
dBase = db;
//Server serv = db.Parent;
updatedobj = db;
break;
case SsbEnum.MessageType:
MessageType mt = null;
if (isEdit)
mt = (MessageType)objToUpdate;
else {
mt = new MessageType();
mt.Parent = sb;
mt.Name = txtName.Text;
}
if (cboUser.Text != string.Empty)
mt.Owner = cboUser.Text;
mt.MessageTypeValidation = (MessageTypeValidation)Enum.Parse(typeof(MessageTypeValidation), cboVal.Text);
if (cboValSchema.Enabled)
mt.ValidationXmlSchemaCollection = cboValSchema.Text;
if (isEdit)
mt.Alter();
else
mt.Create();
updatedobj = mt;
break;
case SsbEnum.Contract:
ServiceContract sc = new ServiceContract();
sc.Parent = sb;
sc.Name = txtName.Text;
if (cboUser.Text != string.Empty)
sc.Owner = cboUser.Text;
//get the message types
foreach (DataGridViewRow row in dvMsgTypes.Rows) {
sc.MessageTypeMappings.Add(new MessageTypeMapping(sc, row.Cells[0].Value.ToString(), (MessageSource)Enum.Parse(typeof(MessageSource), row.Cells[1].Value.ToString())));
}
if (isEdit)
sc.Alter();
else
sc.Create();
updatedobj = sc;
break;
case SsbEnum.Queu:
ServiceQueue q = null;
if (isEdit)
q = (ServiceQueue)objToUpdate;
else {
q = new ServiceQueue();
q.Parent = sb;
q.Name = txtName.Text;
}
q.IsEnqueueEnabled = chkStatus.Checked;
if (chkRetention.Checked)
q.IsRetentionEnabled = true;
//if (chkActivation.Checked) {
//if(isEdit)
// q.IsActivationEnabled = chkActivation.Checked;
//
if (chkActivation.Checked) {
q.IsActivationEnabled = chkActivation.Checked;
if (dBase.Name != cboQDb.Text)
q.ProcedureDatabase = cboQDb.Text;
StoredProcedure sp = (StoredProcedure)cboProc.SelectedItem;
q.ProcedureSchema = sp.Schema;
q.ProcedureName = cboProc.Text;
q.MaxReaders = short.Parse(txtReaders.Text);
if (rdOwner.Checked)
q.ActivationExecutionContext = ActivationExecutionContext.Owner;
else if (rdSelf.Checked)
q.ActivationExecutionContext = ActivationExecutionContext.Self;
else if (rdUser.Checked) {
q.ActivationExecutionContext = ActivationExecutionContext.ExecuteAsUser;
q.ExecutionContextPrincipal = txtExecuteAs.Text;
}
}
if (isEdit)
q.Alter();
else
q.Create();
updatedobj = q;
break;
case SsbEnum.Service:
BrokerService bserv = null;
if (isEdit)
bserv = (BrokerService)objToUpdate;
else {
bserv = new BrokerService();
bserv.Parent = sb;
bserv.Name = txtName.Text;
}
if (cboUser.Text != string.Empty)
bserv.Owner = cboUser.Text;
ServiceQueue servq = (ServiceQueue)cboQueue.SelectedItem;
bserv.QueueName = servq.Name;
bserv.QueueSchema = servq.Schema;
if (lbChosenCtr.Items.Count > 0) {
foreach (object o in lbChosenCtr.Items) {
ServiceContract servctr = o as ServiceContract;
ServiceContractMapping scm = new ServiceContractMapping(bserv, servctr.Name);
bserv.ServiceContractMappings.Add(scm);
}
}
if (isEdit)
bserv.Alter();
else
bserv.Create();
updatedobj = bserv;
break;
case SsbEnum.Route:
ServiceRoute srt = null;
if (isEdit)
srt = (ServiceRoute)objToUpdate;
else {
srt = new ServiceRoute();
srt.Name = txtName.Text;
srt.Parent = sb;
}
if (cboUser.Text != string.Empty)
srt.Owner = cboUser.Text;
if (textBroker.Text != string.Empty)
srt.BrokerInstance = textBroker.Text;
if (textRemote.Text != string.Empty)
srt.RemoteService = textRemote.Text;
if (textLifeTime.Text != string.Empty)
srt.ExpirationDate = DateTime.Parse(textLifeTime.Text);
if (rdLocal.Checked)
srt.Address = "LOCAL";
if (rdTransport.Checked)
srt.Address = "TRANSPORT";
if (rdTCP.Checked)
srt.Address = "TCP://" + txtAddress.Text;
if (txtMirror.Text != string.Empty)
srt.MirrorAddress = "TCP://" + txtMirror.Text;
//StringCollection sColl = srt.Script();
//foreach (string s in sColl)
// MessageBox.Show(s);
if (isEdit)
srt.Alter();
else
srt.Create();
updatedobj = srt;
break;
case SsbEnum.RemoteBinding:
RemoteServiceBinding remBind = null;
if (isEdit)
remBind = (RemoteServiceBinding)objToUpdate;
else {
remBind = new RemoteServiceBinding();
remBind.Name = txtName.Text;
remBind.Parent = sb;
}
if (cboUser.Text != string.Empty)
remBind.Owner = cboUser.Text;
remBind.RemoteService = textRemServ.Text;
remBind.CertificateUser = cboRemUser.Text;
remBind.IsAnonymous = chkAnon.Checked;
StringCollection sColl = remBind.Script();
foreach (string s in sColl)
MessageBox.Show(s);
if (isEdit)
remBind.Alter();
else
remBind.Create();
updatedobj = remBind;
break;
case SsbEnum.Conversation:
TimeSpan ts = TimeSpan.Zero;
Guid grpHandle = Guid.Empty;
string convContract = "DEFAULT";
BrokerService bServ = (BrokerService)cnv_cboFrmServ.SelectedItem;
string toService = cnv_txtToSrv.Text;
if (cnv_txtLifetime.Text != string.Empty && cnv_txtLifetime.Text != "0")
ts = TimeSpan.FromSeconds(double.Parse(cnv_txtLifetime.Text));
if (cnv_cboContract.Text != string.Empty)
convContract = cnv_cboContract.Text;
if (cnv_txtRelGrpHndl.Text != string.Empty)
grpHandle = new Guid(cnv_txtRelGrpHndl.Text);
//get a service object
Service smoserv = smo.GetSSBIService(bServ.Parent.Parent, bServ.Name);
if (smoserv.Connection.State == ConnectionState.Closed)
smoserv.Connection.Open();
smoserv.Connection.ChangeDatabase(bServ.Parent.Parent.Name);
updatedobj = smoserv.BeginDialog(toService, convContract, ts, cnv_chkEncryption.Checked, grpHandle);
break;
case SsbEnum.Message:
SSBIConversation msgConv = (SSBIConversation)msg_cboConv.SelectedItem;
string servName = msg_txtFrom.Text;
//we need a service object
Service msgSsbiServ = smo.GetSSBIService(dBase, msgConv.FromService);
if (msgSsbiServ.Connection.State== ConnectionState.Closed)
msgSsbiServ.Connection.Open();
msgSsbiServ.Connection.ChangeDatabase(dBase.Name);
Conversation msgCnv = new Conversation(msgSsbiServ, msgConv.Handle);
string msgType = msg_cboMsgType.SelectedText;
string msgString = msg_rchMsg.Text;
msgType = msg_cboMsgType.Text;
MemoryStream msgBody = new MemoryStream(Encoding.ASCII.GetBytes(msgString));
Microsoft.Samples.SqlServer.Message msg = new Microsoft.Samples.SqlServer.Message(msgType, msgBody);
msgCnv.Send(msg);
break;
case SsbEnum.Login :
string pwd = "";
Login lg = new Login();
lg.Parent = dbServ.SMOServer;
lg.Name = lgn_txtLoginName.Text;
if (lgn_rdSql.Checked) {
pwd = lgn_txtPwd.Text;
lg.PasswordPolicyEnforced = lgn_chkEnforcePolicy.Checked;
lg.LoginType = LoginType.SqlLogin;
lg.Create(pwd);
}
else {
lg.Create();
}
updatedobj = lg;
break;
case SsbEnum.Certificate:
string certOwner = "dbo";
int certSource = cert_cboSource.SelectedIndex;
Certificate cert = new Certificate();
if(!isEdit) {
cert.Name = txtName.Text;
if(cboUser.Text != "")
certOwner = cboUser.Text;
cert.Parent = dBase;
cert.Owner = certOwner;
}
cert.ActiveForServiceBrokerDialog = cert_chkBeginDlg.Checked;
if (certSource == 0) {
if (!isEdit) {
if (cert_chkMasterKey.Checked)
cert.Create(cert_txtCertPath.Text, CertificateSourceType.File, cert_txtPrivPath.Text, cert_txtDecrypt.Text);
else
cert.Create(cert_txtCertPath.Text, CertificateSourceType.File, cert_txtPrivPath.Text, cert_txtDecrypt.Text, cert_txtEncrypt.Text);
}
}
else if (certSource == 1) {
if (!isEdit) {
cert.StartDate = cert_dtValidFrom.Value;
cert.ExpirationDate = cert_dtExpiry.Value;
cert.Subject = cert_txtCertPath.Text;
if (cert_chkMasterKey.Checked) {
cert.Create();
}
else {
cert.Create(cert_txtEncrypt.Text);
}
}
}
else if (certSource == 2) {
if (!isEdit) {
cert.Create(cert_txtCertPath.Text, CertificateSourceType.File);
}
}
if (isEdit)
cert.Alter();
updatedobj = cert;
break;
case SsbEnum.User :
User usr;
if (!isEdit) {
usr = new User();
usr.Name = txtName.Text;
usr.Parent = dBase;
if(usr_chkLogin.Checked)
usr.Login = usr_cboLogin.Text;
}
else
usr = (User)objToUpdate;
if (usr_cboCerts.SelectedIndex != -1)
usr.Certificate = usr_cboCerts.Text;
if (!isEdit)
if (usr_chkLogin.Checked)
usr.Create();
else
smo.CreateUserWithNoLogin(usr);
else
usr.Alter();
updatedobj = usr;
break;
case SsbEnum.EndPoint :
Endpoint ep = null;
if (!isEdit) {
ep = new Endpoint();
ep.Name = txtName.Text;
ep.Parent = dbServ.SMOServer;
ep.ProtocolType = ProtocolType.Tcp;
ep.EndpointType = EndpointType.ServiceBroker;
}
else
ep = ((SSBIEndpoint)objToUpdate).EndPoint;
ep.Protocol.Tcp.ListenerPort = int.Parse(ep_txtPort.Text);
if (ep_txtIp.Text == "ALL")
ep.Protocol.Tcp.ListenerIPAddress = System.Net.IPAddress.Any;
else {
ep.Protocol.Tcp.ListenerIPAddress = System.Net.IPAddress.Parse(ep_txtIp.Text);
}
ep.Payload.ServiceBroker.EndpointAuthenticationOrder = (EndpointAuthenticationOrder)ep_cboAuth.SelectedItem;
if (ep_cboCert.SelectedIndex != -1)
ep.Payload.ServiceBroker.Certificate = ep_cboCert.Text;
ep.Payload.ServiceBroker.EndpointEncryption = (EndpointEncryption)ep_cboEncrypt.SelectedItem;
if (ep_cboAlgorithm.SelectedIndex != -1)
ep.Payload.ServiceBroker.EndpointEncryptionAlgorithm = (EndpointEncryptionAlgorithm)ep_cboAlgorithm.SelectedItem;
ep.Payload.ServiceBroker.IsMessageForwardingEnabled = ep_chkFwd.Checked;
if (ep_txtSize.Text != string.Empty)
ep.Payload.ServiceBroker.MessageForwardingSize = int.Parse(ep_txtSize.Text);
if(!isEdit)
ep.Create();
switch ((EndpointState)ep_cboState.SelectedIndex) {
case EndpointState.Disabled :
ep.Disable();
break;
case EndpointState.Started :
ep.Start();
break;
case EndpointState.Stopped :
if (isEdit)
ep.Stop();
break;
}
if (isEdit)
ep.Alter();
break;
case SsbEnum.CreateListing :
CreateListing(true);
break;
}
if (isEdit)
_state = SsbState.Edited;
else
_state = SsbState.New;
Cursor.Current = crs;
this.DialogResult = DialogResult.OK;
ExitAndClose();
}
catch (FailedOperationException e) {
smo.ShowException(e);
}
catch (Exception ex) {
smo.ShowException(ex);
}
finally {
if(dbServ !=null)
dbServ.SMOServer.ConnectionContext.Disconnect();
}
}
return 0;
}
private async void LoginButton_OnClick(object sender, RoutedEventArgs e)
{
if (!string.IsNullOrEmpty(PasswordBox.Password))
{
Views.Busy.SetBusy(true, ResourceLoader.GetForCurrentView().GetString("BusyMessage_GeneratingMasterKey"));
// generate master password stuff
var userName = UserNameBox.Text;
var password = PasswordBox.Password.ToCharArray();
await Task.Run(() => App.Container.Resolve <SettingsService>().MasterKey = MasterKey.Create(userName, password));
Views.Busy.SetBusy(false);
Shell.Instance.SetMenuState(HamburgerMenuState.LoggedIn);
Shell.Instance.NavigationService.Navigate(typeof(Views.SitesPage), new SitesPageViewModelParameter {
ParameterType = SitesPageViewModelParameterType.RefreshView
});
return;
}
Shell.Instance.SetMenuState(HamburgerMenuState.Default);
PasswordBox.Focus(FocusState.Keyboard);
}
static void Main()
{
var connectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
ServerConnection cnn;
using (var sqlConnection = new SqlConnection(connectionString))
{
cnn = new ServerConnection(sqlConnection);
}
cnn.Connect();
Console.WriteLine("Connected");
_server = new Server(cnn);
Console.WriteLine("Create the server object");
if (_server.EngineEdition == Express)
{
Console.WriteLine($"Not available on {_server.Edition}");
Console.ReadLine();
return;
}
// Drop the database if exists
if (_server.Databases[_dbName] != null)
{
_server.Databases[_dbName].Drop();
}
//Create the database
var db = new Database(_server, _dbName);
db.Create();
// get the reference to the master database
var masterDb = _server.Databases["master"];
// Drop certificate and master key
masterDb.Certificates[_certName]?.Drop();
//
masterDb.MasterKey?.Drop();
//Create the master key
var mk = new MasterKey {
Parent = masterDb
};
mk.Create(_password);
mk.Open(_password);
//Creating certificate
var certificate = new Certificate(masterDb, _certName)
{
StartDate = DateTime.Today,
Subject = "Certificate to protect TDE key"
};
certificate.Create();
if (File.Exists(_certfile))
{
File.Delete(_certfile);
}
if (File.Exists(_privateKeyFile))
{
File.Delete(_privateKeyFile);
}
//Create a backup of the server certificate in the master database.
certificate.Export(_certfile, _privateKeyFile, _password);
try
{
//Create Database Encryption
var dbEk = new DatabaseEncryptionKey
{
Parent = db,
EncryptionAlgorithm = DatabaseEncryptionAlgorithm.Aes256,
EncryptionType = DatabaseEncryptionType.ServerCertificate,
EncryptorName = _certName
};
// Just show in console the database encryption key status
var so = new ScriptingOptions
{
IncludeHeaders = true,
IncludeIfNotExists = true
};
var sc = dbEk.Script(so);
Console.WriteLine(new string('-', 79));
Console.WriteLine("T-SQL for create");
Console.WriteLine(new string('-', 79));
foreach (var s in sc)
{
Console.WriteLine(s);
}
dbEk.Create();
// Show another feature. How to capture t-sql
_server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteAndCaptureSql;
// Change the database compatibility mode
db.CompatibilityLevel = CompatibilityLevel.Version120;
db.Alter();
// Reproducte T-SQL in Console
Console.WriteLine(new string('-', 79));
Console.WriteLine("T-SQL for Alter");
Console.WriteLine(new string('-', 79));
foreach (var s in _server.ConnectionContext.CapturedSql.Text)
{
Console.WriteLine(s);
}
// Clearing the already stored TSQL
_server.ConnectionContext.CapturedSql.Clear();
// Changing the execution mode back to normal
_server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql;
db.EncryptionEnabled = true;
db.Alter();
}
catch (Exception ex)
{
Console.WriteLine(string.Join(Environment.NewLine + "\t", ex.CollectThemAll(ex1 => ex1.InnerException)
.Select(ex1 => ex1.Message)));
Console.ReadLine();
}
finally
{
//db.Drop();
//masterDb.Certificates[_certName].Drop();
}
if (cnn.IsOpen)
{
cnn.Disconnect();
}
cnn = null;
db = null;
_server = null;
Console.WriteLine("Press any key to exit...");
Console.ReadLine();
}
