I've been searching for information on this for awhile unsuccessully. I am using SQL Server 2005 Enterprise Edititon and transactional replication with separate publisher, distributor and subscribers. I need to encrypt data on our publisher and then I need to replicate that data out (i.e., encrypted data, not encrypted transmission). Since the keys are server specific, I imaging there might be some difficulty in doing this, but I figure I can't be the only one with this problem. Does anyone have a resource or know what must be done to get this accomplished?
I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.
select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a
The Following Error I get When I execute the above statement.
Msg 195, Level 15, State 10, Line 1
'Encrypt' is not a recognized built-in function name.
I am using Sql 2005 and merge replication. I am relying on the feature where schema changes are replicated to subscribers but I have come across a situation where schema changes stop being replicated.
This is the scenario:
I create a database and publish it for merge replication.
I add subscribers.
If I need to change the published database I can use ALTER TABLE ddl and the subscriber gets the changes.
If I have to add or remove a merge article as part of a database change I specify the @force_invalidate_snapshot=1, @force_reinit_subscription=1. No any ALTER TABLE statements following the article change will NOT be replicated.
Is this a known 'feature'? Is it because @force_reinit_subscription is set to 1?
I am wondering if there is a way to replicate changes in a SQL 2000 DB to 2005 without backing up the DB and restoring it in 2005. We are running an ERP system using SQL 2000 and are moving to a later version that supports 2005 and we want to test it out before going live but I'd like to sync with the current system from time to time instead of having to convert the DB and get it ready again and again everytime I want to update the data. Thanks for any help you can offer, Chad
I am very new to SQL Server. Plenty of SQL knowledge but the whoe SQL server enviornment is new.
I am working with SQL Server 2005. My task is to generate reports without affecting our live database. I have setup a second server and installed SQL Server 2005 on that too. My thought was that maybe I could mirror or replicate the table I require over to this new server and run my queries from here. Is this easy to do ?
I read that mirroring might not work as it is solely for back up /fall over purposes and that data on the mirrored server would not be accessible.
I have also been looking at SSIS but at the moment this is all a bit like double dutch to me ! Can anyone point me in the right direction, preferably somewhere beginner friendly ie not overly complicated !!
We have four mobile devices that are set up for merge replication via the web. We are not receiving errors but some of the data is not coming over to the devices. If we manually add a record that record will come over, but there is data that is on the server that isn't on the devices. If we run the snapshot for each device (We're using host_name as a filter) nothing happens. If we do validation check we get errors. If we reinitialize all devices it works but the next days data (sql job populates data to the publisher db at night) isn't on the device after syncing the next morning. Any help would be appreciated.
We have a database that I would like to replicate on another server but am unable to use regular replication via publish/subscribe due to the fact that the production database has no primary keys on tables, only clustered indexes. The backup db needs to be as close to real-time synchronized as possible and will be in fairly active use most of the time. Has anyone had success in developing such a system? How did you do it and what are the pitfalls? Any advice would be greatly appreciated. Thank you. W.
I have SQL Server running on my internal LAN. I want to have a second SQL Server running on a hosted (shared) website. I then would like these servers to talk to each other. At some scheduled time I need to publish data to the web, and I then need to subscribe to data input on the web by various clients. My internal LAN can see the Internet via our cable modem.
What is the best way to do this? What software will I need to run. I'm looking for the big picture.
Basically I need to replicate 2 tables from a Visual Fox Pro application to SQL server 2005 From an Intranet to a hosted Center. (Different domains) Is this possible? Any suggestions?
I have a SQL 2005 database that I am using with a website. This basic website will be sold to other companies and ran on their servers with different URLs. Since, All of these databases will store different data, I'm not sure how I can make updates to original database and replicate those structure changes to the other DBs without changing the data also. Is there a way to automate the replication of structural DB changes without replicating the data along with it?
It appears that every 5 or so days, my transactional replication is hanging. I see that I have a couple thousand undistributed commands and this number keeps growing. It forces me to generate a snapshot in order to sync up. When generating the snapshot, I will get frequent messages like "waiting for a response from server...". So I will keep stopping and starting syncronization. Eventually the snapshot will go out, with a delay after generation.
I may have a requirement to send data from a SQL Server at site A to an Oracle server at site B. These sites have no network connection between them, and the current suggestion is to use ftp, but the transfer (or username and password) will not be encrypted.
If I create a DTS package transferring data from site A, will that transfer be encrypted?
If not, is there an option with SQL Server DTS to ensure that the data is sent in an encrypted form?
In 2007 we plan on moving many of our SQL Servers (2000 & 2005) to a SAN. We will also be investigating the best way to replicate data from our production SAN to a Disaster Recovery SAN. I would like to know if people rely on built-in SQL Server replication / log shipping or third party products like DoubleTake or MirrorView. What's the advantage, if any, to using a third-party product?
I just finished reading an article on how to search encrypted data efficiently and they suggested creating a new column with a Message Auhtentication Code. To be honest, reading the aritcle makes my head hurt. I can hardly understand what they were doing myself and I can't begin to explain it to a developer.
Are there any easier ways to search encrypted columns for a speciifc match? If not, does any have some stored procs that implement this messy MAC stuff?
I need to implement SQL Reporting Services but do not wish to use the production database for running the reports against. I intend to set a seperate server for reporting services. The data only needs to be refreshed on a daily basis, during slient hours. What would be the best way to copy data from the production to the reporting services server?
How do you setup replication from SQL Server 2000 to a oracle database? We want to push changes from sql sever to oracle, evertime sql server table is changed. I have a working ODBC connection. When I try setting up a push to the oracle side I can not find the subscriber In the list. Can someone point me in a direction, with a detailed way of doing this.
I have been trying to replicate from Sql Server to Postgres using Enterprise Manager and the Postgres ANSI ODBC driver version 8.02.02.
With enterprise manager, I was able to copy tables over when I used the option to "DROP existing table and re-create it," but columns of data types datetime and decimal were copied as varchar.
So I tried drop the tables manually in Postgres and then create them with the proper data types (timestamp without time zone, and numeric). I then tried changing the article option to "Keep the existing table unchanged" and I get the following error from the Distribution agent: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
So I then tried using the article snapshot option "Delete all data in the existing table" and I got the same error.
Anyone has any luck replicating in this fashion? Or should I maybe go about it differently?
I am using a sql server 2012 and having a database "Test".In this database , having a table "Employee". I have encrypted the employe table data with the password. How can is use these employee data in oracle server. ie;How can I show the employee data as the encrypted and decrypted form of data in Oracle server( The actual encryption happened only in SQL DB).
I have two databases, one production and other a production copy.
One column of the production table is encrypted by the following syntax.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'
CREATE CERTIFICATE cert_demo
WITH SUBJECT = 'encryption demo'
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE cert_demo
-----------------------------------
I have stored procedure to add records to a production table like this.
ALTER Procedure AddRecord
@id int,
@var1 varchar(100)
AS
BEGIN
DECLARE @var2 varbinary(256)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
SET @var2 = EncryptByKey(
key_guid( 'symkey_demo' ),
@var1 )
insert into dbo.Test(id,Data,EData)
values(@id,@var1,@var2)
CLOSE SYMMETRIC KEY symkey_demo
END
---------------------------------
Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.
Create procedure ViewData
@id int
AS
BEGIN
DECLARE @var1 varbinary(256)
DECLARE @var2 varchar(100)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
select @var1=EData from Test where id=@id
Set @var2 = convert( varchar(100), DecryptByKey( @var1))
print @var2
END
It doesn't print the enctypted data. Please suggest what goes wrong.
I got a problem concerning encryption. The thing is I have decided to use symmetric key protected by certificate to encrypt certain information. Certificates are protected by database masterkey and by service key.
But I also want to be sure that if someone steals my database with all its data he wont be able to decrypt it with his own SQL Server Management Studio where he has all the permissions.
Also after some time I will need to take my database and set it up on another PC.
Has anyone ideas how to solve this??
P.S. As far as I know if symmetric key is protected by certificate which is protected by DB master key and service master key then you cant decrypt data if database is moved to another workstation and opened with another Management Studio. Please can anyone explain how this works( if its true). And if this is true then how can i move my DB without loosing access to encrypted data???
I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.
How do I do this?
I have searched the forum for some ideas, but did not find any.
I need to upload bulk data into a datatable. Some of the source data will already exist in the target table to be updated. How can I INSERT only the new data into this datatable? Thanks.
I am new to database programming and was curious how others solve theproblem of storing encrypted in data in db table columns and thensubsequently searching for these records.The particular problem that I am facing is in dealing with (privacy)critical information like credit-card #s and SSNs or business criticalinformation like sales opportunity size or revenue in the database. Therequirement is that this data be stored encrypted (and not in theclear). Just limiting access to tables with this data isn't sufficient.Does any database provide native facilities to store specific columns asencrypted data ? The other option I have is to use something like RC4 toencrypt the data before storing them in the database.However, the subsequent problem is how do I search/sort on these columns? Its not a big deal if I have a few hundred records; I couldpotentially retrieve all the records, decrypt the specific fields andthen do in process searches/sorts. But what happens when I have (say) amillion records - I really don't want to suck in all that data and workon it but instead use the native db search/sort capabilities.Any suggestions and past experiences would be greatly appreciated.much thanks,~s
I have read recommendations about searching encrypted data. Typically, they involve creating a MAC (message authentication code) table. One of the elements of that table is a HASH of the encrypted data (plus a Mac key) that is used as an index for searching. Is that HASH as secure as the encrypted data itself, or is this approach less secure? If it is less secure, then may I assume that approach is the only feasible way to search data encrypted by nondeterministic algorithms?
Hi, I have database with encrypted column data in my customer server. Sometime, I might need to backup their database back to office for troubleshooting.
How could I backup/restore master key, symmetric and asymmetric key created for my database?
Hi, I am trying to lean ASP.NET. So I was trying to install SQL Server too. But, I could not install. The aleart box popped up:
"SQL Server set up cannot install files to the compressed or encrypted folder: C:Program FilesMicrosoft SQL Server"
Before this, I also got a warning that the hardware of my computer does not meet the hardware requirement.
I would like to know how I can install SQL Server into my computer. Or, shouldn't I do that? To learn ASP.NET I want to have a database ready. If I can not use SQL Server, I may try Access or something smaller...
I am trying to replicate data from a view in the publisher to a table in the subscriber (transaction replication). I do not need the view's base table, or the view itself, replicated to the subscriber. I only want to data from the view to feed a table in the subscriber.
I am using SQL Server 2008 R2.I have opened the Reporting Service Configuration Manager to delete the encrypted data.In the encryption key section, when I click on the 'Delete' button to delete the encrypted data, it gives me below error. Microsoft.Reporting Services. WmiProvider. WMIProvider Exception: An error occurred when attempting to connect to the report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the operation.
---> System.Runtime.InteropServices.COMException (0x800706B3): The RPC server is not listening. (Exception from HRESULT: 0x800706B3) --- End of inner exception stack trace --- at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo) at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.DeleteEncryptedInformation() at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()
