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