With the new functionality provided by sql server 2005, can we use the certificate functionality to act as a db store for third party certificates. Posts that I have seen so far indicate that there is no direct access to the certificate store once a certificate has been imported to the db. Given that the database is more portable (for failover purposes for certificate storeage, lack of access to the certificate store should be considered a considerable problem for ISV's.
Failing that, if we need to impliment this functionality ourselves what is the best format to store certificates in the database (blob or other)
I have access to a microsoft certificate server and I have generated a server certificate for use in my SQLexpress installation. The certificate was installed into the personal folder of the local machine. The MMC certificate snap in can see it fine.
When I use the SQL server configuration manager and look in the certificate tab and try to use the drop down to find the certificate so SQL express can use it, nothing shows up.
Can someone please give instructions on how I can get SQl express to use the server certificate that I just installed?
Does SQL Server support wildcard Certificates. When you install the wild cert in the certificate store, the sql configuration manager does not see it in its drop down list. Id it does, what are the steps or please point me to the right direction. Does the cert need to be specifically for that particular hostname. Thanks
I am looking for a good introduction into the handling of certificates in SQL 2005.
I need to sign a procedure to allow it to access a dm view. I do understand the theory and the syntax, but I have trouble coming up with an easy but still secure way to create these certificates on all customer servers without allowing misuse. All articles I could find are going through creating a new database, setting up a certificate with or without password, signing an example proc and then dropping the database. Non seems to care about the problems that occur later on during the life of a certificate.
I want to get all the student from a Database table which store student certificate. For example, I need a query of student who "HAVE" 3 certificate(Cert A, B, C).
Certificate Table:
StudentName CertificateName John Cert A Wilson Cert B John Cert B John Cert C Michael Cert A
Output: John
sqlQuery = "Select * from CertificateTable Where (CertificateName = 'Cert A') AND (CertificateName = 'Cert B') AND (CertificateName= 'Cert C')"
Need to replicate mobile device that uses a wildcard certificate. Heard that ms windows mobile 5.0 does not support wildcard certificates. Is there a solution around this using vb.net.
I just want to be 100% sure about something. Certificates generated for use with service broker endpoints must be generated in the maste database, correct?
What are the implecations of the master key is changed for the master database ?
This may seem like a stupid question but i am trying to get the hang of the new security model.
I have not really heard anything mentioned about certficate expiration date when it comes to creating certificates for keys or service broker endpoints etc.
We have created certificates for keys and service broker endpoints, now what exactly happens when the expiration date, by default 1 year i think is reached, will we no longer be able to decrypt encrypted data and will the service broker endpoints stop working etc ?
Or is this expiration date when the certificate can no longer be used to create security objects ? And all security objects already created with this certificate will always work ?
In other words is there ever danger that keys and endpoints or basically any object referrencing this certificate will just suddenly stop working one day, or will all objects work indefinately regardless of an certificate/objects expiration date ?
I am currently trying to replicate a SQL Mobile 2005 database with a SQL Server 2005 database through web synchronization using SSL Server AND Client Certificates. On IIS, with "Require Client Certificates" unchecked, I can replicate fine. Once I turn it on, I get a message from replication saying "A Secure Socket Layer connection is required to access this site". I have installed a client certificate in IE, and can access the https://servername/Ojt/sqlcesa30.dll site (I tried removing the client certificate, and I was denied access, then reinstalled it and it worked - so I think that part is working). Does anyone have any experience with this? My production operating environment requires client-side certificates.
Hi, i want to know if its posible to create credentials or certificates in order to protect a SQL 2005 data base.
Because if someone Buckups one of my DBs from my server, and try to restore it in orther server i dont want they to see my DB information because he dont have the correct credentials or certificates for it.
I am trying to use the example in http://support.microsoft.com/kb/915852. This creates two databases SourceDB and TargetDB. If I put SourceDB on the same SQL Server instance as TargetDB, the messages is received with no problem. If I put the SourceDB on another Server so than I am using two separate servers in the same domain, the message never gets to the TargetDB. I have changed the routes to the correct server names and set the route port to 8286. CREATE ROUTE [myRoute] WITH SERVICE_NAME = 'SourceService', address = 'TCP://toto:8286'; and: CREATE ROUTE [myRoute] WITH SERVICE_NAME = 'TargetService', address = 'TCP://devbox05:8286';
My SourceDB is on one of several instances on the server toto. My instance is totofoxylady01,52005. The certificates were generated using the passwords in the article.
The project I'm currently working on has to be relatively simple for the users to install, so I had the idea of using service broker to "set itself up". The idea is that there will be a server with a service and suchlike already set up. Somehow the clients will get the information required to create a remote service binding, and once that has been accomplished will send a registration message to the server.
What I'm wondering is can I somehow create and send a certificate using service broker via t-sql, and also is this approach reasonable or is it a bad idea. The reason I had thought of something like this is because the people setting up the client sites may (and probably will) have very little knowledge (probably no sql server knowledge) so we need the installation to basically be a click and install.
Any way to bulk export / import TDE Certs? I've got a bunch of databases that need to be moved to another system. Just about every database is using TDE and was wondering if there was a way to move these certs in a bulk fashion. I've got SQL and Powershell scripts to backup and restore multiple databases, but won't do me any good without the certs.
I am trying to set up SQL Server 2005 to use a test certificate from Thawte for SSL encryption. I have installed the certificate in the local computer, current user and service account's personal certificates folders. I have also installed the root certificate in the Trusted Root Certification Authorities folders of each. All this was done using MMC.
However, when I go to tell SQL Server to use the certificate, no certificates show up in the drop down box. I am using SQL Server Configuration Manager and doing the right-clickPropertiesCertificate steps shown in multiple KB and forum articles.
I have also checked the registry entry at HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServerSuperSocketNetLibCertificate. It is blank, which tells me SQL Server should be looking in the certificate store.
If I simply set the ForceEncryption flag, SQL Server starts up OK and generates a self-signed certificate. Using that, the session does get encrypted. However, I need to use a third party certificate.
I have hit a brick wall and am at a loss. Any help would be greatly appreciated.
I am looking at documentation of CREATE CERTIFICATE statement. I am having hard time in understanding if I want to create CERTIFICATE with above mentioned options, how I am supposed to create either PRIVATE KEY file or EXECUTABLE file. any example would be really helpful for what I am doing here.
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
I need to set up asyncronous data replication across two clustered instances of SQL 2012 across 2 Datacenters. Both the datacenters have a common domain however the vlans are different. There are only 3 small databases on the primary instance.
any issue in setting up mirroring in this case as vlans are different.
Hi All By mirroring I mean device mirror, since I do not think that there is a database mirror We only have disk mirror. I also know that SQLServer 7.0 does not provide Mirror, unmirror or remirror. and for this we have to refer to Windows NT disk mirroring. I need mirroring in both versions SQL Server 6.5 and 7.0
I want to have mirror on another server. I need this for tonight. Sincerely.
I hereby let u know the steps that i undertook for performing mirroring
1. Created a database in Principal server named Mirror1 and one table named tblMirror1.( no values inside the table)
2. Repeated the same in Mirror in Witness
3. Selected the Principal server's database -->right click-->tasks-->mirror
4. from the left hand panel selected Options -->recovery model-->full
5. selected Mirroring from left hand panel-->configure secuirty-->entered mirror and witness name--Success
6. When I click on start mirroring I have an error stating
" Alter failed for database mirror1"
"Database mirroring cannot be enabled because Mirror1 database may have bulk logged changes that have not been backed up etc...."
7. followed this one BACKUP database mirror1 TO DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'
got the answer as
Processed 160 pages for database 'mirror1', file 'Mirror1' on file 2. Processed 1 pages for database 'mirror1', file 'Mirror1_log' on file 2. BACKUP DATABASE successfully processed 161 pages in 0.318 seconds (4.147 MB/sec).
According to sources Run a restore of this backup on your mirror. This can be done through the GUI or using a T-SQL command.
The database restore must use the NO RECOVERY option, so the database stays in a loading state.
Also the database name on the mirror must be the exact same name as the principal
RESTORE TestMirror FROM DISK='C:Backup TestMirror_FULL.BAK' WITH NORECOVERY
Hence did the below
RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY
ERROR
Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.). Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
HENCE MODIFIED AS
use master go RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY
Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.). Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
i am for the first time creating the database mirroring.I am moving the all the usr databases and everything from test server to a new server called test1.And from that new server i am restoring on the another new server called test2 for database mirroring.But in the test server , the transaction logs are backup for every 15 minutes.So in this case is there a need to move all the logs to test1 and then to test2 for mirroring?? or there is no need to move transaction logs to the new servers at all??
my secnario ------------ server1 server2 server3 -------- ------- -------- test test1 (moving everything from test) test2(mirror for test1 )
all the transaction logs are backup in test for every 15 minutes. so in this case , do i need to move the transcation logs from test to test1 and test2 or there is not need to move at all??
When i set up database mirroring, do i have to set up Principle, mirror and witness on seperate servers as well as seperate instances.
Or can i install 3 seperate instances on the same laptop im using and still use mirroring? I have tried to start mirroring with all three instances on my laptop but i receive an error.
I am trying to set up database mirroring and in the initial phase of database restoring im getting some problem.
the scenario is .
1- I create a TestMirror on server1(which will be principal server) and backup full and transaction logs.
2- create TestMirror in server 2( Which is going to be used as mirror) .
3- restore the full backup of "TestMirror" from Principle Sever with NORECOVERY option.
this step results in a message box showing that the restore was successfull...
but the database explorer( management studio) shows the TestMirror in (restoring ...) state. and no more operations can be performed with this Database. ( because the database is in restoring mode).. please help what this issue is?
Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online.
Hello, I have Microsoft SQL SERVER 2005 installed in my system. To try out mirroring, I rightclicked on the database went to the tasks, but i found that in Status its written
"This database has not been configured for mirroring"
I am new to DB mirroring and witness. For the purpose of learning, any one can suggest me like is there any posibility to do the DB mirroring and witness on 2 or 3 VHD Files. Basically, these VHD's are downloaded from microsoft website.
Can any one help I want SQL to email me when a database fails over I can find alerts for loads of thing to do with Mirroring but not to notify me when a database failover.
Do you know if the following excerpt from technet is still true? I want to mirror a device over the net to another server. Has anyone tried this with sql server 6.5 or is it still not a feature? ---------------------- PSS ID Number: Q115043 Article last modified on 04-29-1997 4.2 | 4.2 OS/2 WINDOWS
================================================== ==================== ---------------------------------------------------------------------- The information in this article applies to:
- Microsoft SQL Server version 4.2 for OS/2 - Microsoft SQL Server version 4.2 ----------------------------------------------------------------------
The use of database devices accessed over the network is not a supported configuration for either the OS/2 or Windows NT versions of Microsoft SQL Server. All database devices, including mirrors, should be configured to use local drives. Database dump devices are an exception to this restriction. ---------------- Thanks. Gail Wade Raymond James and Associates Clearwater, FL gwade@is.rjf.com
