SQL 2012 :: Encrypting A Column On TDE Enabled Database

Sep 23, 2015

I have a database that is the publisher in transactional replication and also part of an availability group. I have put the pertinent certificates on all of the involved servers, and it is encrypted on all servers and operated as expected. However, we are adding additional security for personal data and we have targeted columns in multiple tables for column encryption. I have a master key and certificates that are stored in the master database. Following an example where I am to create the database master key:

-- Create database Key
USE encrypt_test;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
GO

But when I try to create a certificate on the database:

-- Create self signed certificate
USE encrypt_test;
GO
CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Protect Data';
GO

It get the following:
Msg 15151, Level 16, State 1, Line 1
Cannot find the certificate 'Certificate1', because it does not exist or you do not have permission.

Can I add a database certificate to an already TDE enabled database and if not to I create the symmetric key through the certificate located on the master database? And how will that effect decrypting the column values in stored procedures and function on the user database?

View 0 Replies


ADVERTISEMENT

SQL 2012 :: Encrypting A Column?

Jun 18, 2015

encrypting a column in a sql server database. I have userid and password columns that I think should have protection on them. My thought was to encrypt these 2 columns. What happens if someone needs to know the actual values of a userid and password? Is encryption the right way to secure this data?

View 2 Replies View Related

SQL 2012 :: Encrypting Master Database And TempDB

Sep 30, 2014

Is it possible to encrypt Master database and tempdb? On executing below query result is showing temdb is encrypted.

SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys

View 1 Replies View Related

SQL 2012 :: Database With Filestream Enabled?

Jul 16, 2015

We have a server with a database with filestream enabled. The filestream data is in a filegroup with three files spread across 3 LUNs F:, G:, and H: each with a capacity of 1.8 TB.

The file stream containers in those three LUNs reference the same column in the same table.

The F: Drive has only 64 GB free space left. The H: However has around 700 GB free.

We are looking to move some filestream content from the container in F: to the container in H:.

View 2 Replies View Related

SQL 2012 :: Data Movement On TDE Enabled Database

Feb 13, 2015

I am aware that TDE protects data at Rest and not during communication or data in motion (UNLESS you use Encrypted communication channels using SSL certs etc). Hence I am thinking of doing data export from a TDE encrypted database to a database on the instance where TDE is not enabled or supported. I believe it works and need to take care of relationships between tables.The target database is hosted on SQL 2012 standard edition on which TDE is not supported.

View 4 Replies View Related

Encrypting Column

Mar 20, 2002

Is it possible to encrypt data in one column. I always thought it wasn't possible but I need to know for sure.

View 1 Replies View Related

Encrypting A Column

Oct 22, 2007



I am trying to encrypt a column, and then do a select and decrypt the results.
I believe that I am encrypting the data properly, but whenever I try and do a select the column comes back as NULL.
If I do a select without trying to decrypt the column (select * from table) I get the following:

Name | Dept | Encrypted_Info
-----------------------------------------------
scott | it | 0x38383131


If I try and decrypt it I get this


Name | Dept | Encrypted_Info
-----------------------------------------------
scott | it | NULL

This is the query that I put together to create the database, table, DB Master Key, Cert, encrypt the column, and then select and decrypt the result:


create database QA_Encrypted

Go

create table QA_Encrypted.dbo.Enc_Table1

(UserName varchar(50),

Dept varchar(50),

Encrypted_Info varbinary(MAX))

Go
use QA_Encrypted

Go

if not exists (select * from sys.symmetric_keys where symmetric_key_id = 101)

create Master Key encryption by password = 'Pa55w0rd'

Go

Create Certificate QACert

with subject = 'Certificate for Testing'

Go

Create symmetric Key QAEncryptionKey with Algorithm = DES

Encryption by certificate QACert

Go

open symmetric key QAEncryptionKey decryption by certificate QACert

update QA_Encrypted.dbo.Enc_Table1 set Encrypted_Info = EncryptByKey(Key_GUID('QAEncryptionKey'), 'AUTHENTICATOR')

Go

use QA_Encrypted

insert into dbo.enc_table1 values ('scott', 'it', Convert(varbinary,'8811'))


OPEN Symmetric Key QAEncryptionKey

Decryption by CERTIFICATE QACert

select * from dbo.Enc_Table1

Select Username, Dept, Convert(varchar, DecryptByKey(Encrypted_Info)) as "decrypted"

from dbo.Enc_Table1


Any help would be greatly appreciated.
Thanks in advance

View 3 Replies View Related

SQL 2012 :: Encrypting Client Connection From SSMS

Oct 28, 2015

I used the selfssl.exe tool to generate a certificate on my db server, successfully added to Personal Certificates in MMC, added R/W permissions for the service account to the cert, selected the new certificate in the protocols for mssql and restarted the service. So far so good, however when trying to connect from SSMS with encryption check box checked I get the following error:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

The certificate chain was issued by an authority that is not trusted.

When I look in MMC, the only intended purpose for the certificate is "Server authentication" and I'm not able to add "Client authentication". Does selfssl.exe not support client authentication?

View 2 Replies View Related

Encrypting A Column In SqlServer

Feb 22, 2001

I need to encrypt one column of a Sql Server 7 database. I would like to use some sort of function to accomplish this so regular reporting tools can grab the encrypted data.

View 1 Replies View Related

Encrypting Column Values

May 19, 2007

I want to store a function in SQL Server database that when called during insertion, encrypts a value and then stores encrypted text in the column.

Is there any built in Encrypting and Hashing mechanism in SQL 2005.

View 1 Replies View Related

Encrypting The Column Or Row Data In SQL SERVER 20

Apr 4, 2008

hi i am doing the project in sql sserver 2005
so i want help from u
the following topics i want
"HOW TO ENCRYPT THE DATA IN COLUMN OR ROW IN sql server 2005"
PL REPLY SOON
MY MAIL ID IS

sarunprasadmtechis@gmail.com
if u get any idea pl send mail to the above address pl..

arunprasad

View 1 Replies View Related

SQL Server 2012 :: Script To Reorganize All Enabled Indexes

Jul 30, 2015

My index reorganise maintenance plan fails partly due to the disabled indexes

Executing the query "ALTER INDEX [I_ModelSecurityCommon_RECID] ON [dbo]...

" failed with the following error: "Cannot perform the specified operation on disabled index 'I_ModelSecurityCommon_RECID' on table 'dbo. Model SecurityCommon'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I don't want to delete the indexes as they are standard indexes that where on the DB from install.. any script that will reorganise all enabled indexes? and also to rebuild?

View 5 Replies View Related

SQL 2012 :: Queue Activation Is Enabled But No Receive Executed

Sep 14, 2015

Our Sql server is not responding, So we restarted the server and modified one of the sp code. After that we are getting frequently every 2 min giving the below error

The queue 855365233 in database 9 has activation enabled and contains unlocked messages but no RECEIVE has been executed for 453199 seconds

View 0 Replies View Related

Encrypting The Database

May 29, 2000

Is there any way by which a SQL Server database can be encrypted.

Regrads

View 5 Replies View Related

Encrypting A Whole Database

Oct 22, 2007



I have been tasked with encrypting an entire database, and I need some assistance.
Everything that I have looked at leads me to believe that the only way to encrypt a whole database is to encrypt each column seperatly. I am completely stumped and could really use some help.
Is it possible to encrypt a whole database? if so, how?
If not, what other options do I have?
Thanks in advance for the help.

View 4 Replies View Related

SQL 2012 :: Powershell Script To Verify Showplan Enabled For User?

May 18, 2015

I am looking for a sample PowerShell script that allows me to verify that showplan is enabled for a user on a SQL Server 2012 instance. Haven't figured out how to code it.

View 2 Replies View Related

Encrypting Complete Database

Nov 12, 2007



Hi,

How can I encrypt / decrypt whole database (data, objects ... everything) in SQL Server 2005 Express Edition?
Quick solution from any champion of Databases would be highly entertained.

Waiting ..

Thanks a lot.

View 4 Replies View Related

SQL 2012 :: Calculate TempDB Size For Read Committed Snapshot Enabled

Apr 14, 2014

I receive Error: 3967, Severity: 17, State: 1. Insufficient space in tempdb to hold row versions. We have 8 data files for temp db of 10210 GB size and given 10240 GB as max size.

As MS suggest to calculate the temp db file size and growth rate we need to monitor the perform counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object.

basic formula: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction

My report disk utilizations says tempdb is full ? I thonk I need a shrink for the file .

Still I am confused in calculating the size , My perform counter gives me data as such

Free Space in tempdb (KB)               279938496
Version Generation rate (KB/s)           53681040
Version Cleanup rate (KB/s)       53422320
Version Store Size (KB)      258720
Version Store unit count        22
Version Store unit creation                      774
Version Store unit truncation         752

View 4 Replies View Related

SQL 2012 :: Cannot Truncate Table Because It Is Published For Replication Or Enabled For Change Data Capture

Jul 8, 2015

I get the following error message when a job calls a Stored Procedure that TRUNCATES a Table:

Cannot truncate table 'CombinedSurveyData' because it is published for replication or enabled for Change Data Capture

Is my only option to change the TRUNCATE to DELETE?

[URL]

View 2 Replies View Related

SQL 2012 :: Local Backup Strategy On Primary With Transaction Log Shipping Enabled To Secondary DR Site

Oct 2, 2014

I have a scenario where a customer is going to be using Log Shipping to the DR site; however, we need to maintain the normal backup strategy on the current system. (i.e. Nightly Full, Every 6 Hour Differential and Hourly Transaction Log backup)I know how to setup Transaction Log Shipping and Fail-over to DR and backup but now the local backup strategy is going to be an issue. I use the [URL] .... maintenance solution currently.

Is it even possible to do regular backups locally keeping data integrity for your backup strategy with Transaction Log Shipping enabled?

View 2 Replies View Related

Database Mirroring Cannot Be Enabled

Mar 13, 2008

I am trying to mirror a database and I keep getting the error Msg 1469 saying that "Database mirroring cannot be enabled because the database is an auto close database on one of the partners. Well.. the database properties is saying "false" under auto-close. What else could be wrong?

View 12 Replies View Related

Web-enabled Access Database Question

Jul 12, 2004

Hi all,

I am trying to get a MS Access Database to work on my companies intranet. Everything works fine on my laptop with local file paths. After transferring the files to the web server and changing file paths, I get the 2 following errors.

"Data provider could not be initialized"
"Not a valid file name"

I assume that I am having a problem relinking the database, but unsure of how to do this.

All help is greatly appreciated.

View 4 Replies View Related

How To Check If DB Constraints Are Enabled In A Database?

Mar 6, 2006

How to check if DB Constraints are enabled in a database?

View 6 Replies View Related

Recovery :: Possible To Configure Log Shipping On CDC Enabled Database?

Nov 13, 2015

Is It Possible to Configure Log Shipping On a CDC Enabled Database?

View 4 Replies View Related

Msg 9772 -- Service Broker In Database Cannot Be Enabled

Nov 29, 2005

I backed up a database "Broker", and restored it on the same instance as "BrokerQA"  (Broker db still exists, I need them both running on the same instance).

View 4 Replies View Related

SQL 2012 :: Dumping Fields In A Single Column Of Database?

Apr 3, 2014

While working with a vast variety of support projects, i find a sql design where all the fields in a single form (say about 100 fields which are dump data as they are not related to any reports and searching criteria) are dumped in a sql database column in a XML format. See below an example

<?xml version="1.0" encoding="utf-8"?><FormBuilder><ClientID>0</ClientID><SiteID>0</SiteID><IncidentType></IncidentType><IncidentCategory></IncidentCategory><IncidentSubCategory></IncidentSubCategory><CreatedBy>2</CreatedBy><CreatedOn>Wednesday, April 02, 2014</CreatedOn><ModifiedOn /><ModifiedBy /><Section SectionID="ASD" SectionDisplayName="ASD" ColumnType="1" IsDeleted="0" SectionPosition="1"><SectionField FieldName="Bro" Section="ASD" ModuleID="0" Length="" PickData="" ChkData="" RadioData="" ListData="" FieldType="Text" Checked="false" ColumnType="1" IsDeleted="0" CoulmnOrder="0" FieldID="1" IsPrimary="" IsMandatory="" SystemMandatory="" RowPosition="1" FullRow="" /></Section></FormBuilder>

Just want to know the comments how far is this design feasible.....

What are the pros and cons of such a design...

Where we should use such type of db design where are the fields are dumped in a single column...

View 9 Replies View Related

Data Warehousing :: Find CDC Enabled Time In Database

Aug 11, 2015

How to find the CDC enabled date and time  in database.

View 3 Replies View Related

Proper Way To Backup And Restore Broker-Enabled Database

Aug 29, 2007

For developers, we often have a need to backup a production database and restore it on local or integration machines. This production database is enabled for service broker and operates at a relatively high traffic level. When the database is backed up, the size is nearly 12GB; when SET NEW_BROKER is subsequently executed on the restored database, the size goes down to about 800MB. It appears that most of this is residing in the xmit queue. So, my question is: how best to backup a production database with queues activated, etc. without ending up with a 12GB backup?

Thanks.

View 3 Replies View Related

Compact Server Database-file Not Enabled For Replication

Apr 23, 2008

When I try to create a subscription to my SQL Server Compact 3.5-database file, it gives an SqlCeException-message that says that the file is not enabled for replication. How do enable it?

My SQL Server Management Studio won´t connect to my compact server file right now, so that method is not an option for me right now.

View 1 Replies View Related

Encrypting Column Data While Doing Data Transfer

May 8, 2007

Hi,

I have a SSIS project where I am transferring data from DB2 table to SQL Server table. There is a column called REC_ID which I need to encrypt before we store it in SQL Server. Now, SQL Server has buildin encryption functionality and we need to use that as there are views that will decrypt this column and give data to authenticated users.

So, the question is, is there anyway that I can encrypt the column data in my SSIS package using my target SQL server database key and using SQL server encryptbykey function while transferring?

Thanks,
Ujjaval

View 1 Replies View Related

SQL 2012 :: Query Based On Column Name / Setting Up Database Table

Oct 30, 2015

I have a set of data spread across a number of tables regarding stock market data. An example of this follows:

Market Capitalization...

Date CompA CompB
01/01/11 100 5
02/01/11 102 4

Share Price....

Date CompA CompB
01/01/11 100 100
02/01/11 101 99

Event Data...

Date Company
01/01/11 CompA
02/01/11 CompB

Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.

I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.

For instance:
SELECT Event.Date, Event.Company
FROM Event

how do I now say.....

SELECT MarketCapitalisation.Column
WHERE Column = Event.Company
AND MarketCapitalisation.Date = Event.Date.

I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).

I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.

View 1 Replies View Related

SQL Server 2012 :: Count From Multiple Tables For Same Column Exists In Database?

May 19, 2014

i have database which has 25 tables. all tables have productid column. i need to find total records for product id = 20003 from all the tables in database.

View 9 Replies View Related

Transact SQL :: Index Maintenance / Defrag Fails On CDC Enabled Database - 2008 R2

Oct 18, 2012

We have a new database with cdc enabled on all of its tables.  This causes the index maintenance task to fail with following message:

"Executing the query "EXEC DBName.dbo.IndexDefrag_sp" failed with the following error:  "The unique index 'PK_TableName' on source table '[dbo].[TableName]' is used by Change Data Capture.  To alter or drop the index, you must first disable Change Data Capture on the table.  The transaction ended in the trigger. The batch has been aborted.".  Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly" We would like to run the index maintenance without losing the cdc data.  We plan on installing SP2 on SQL Server 2008 R2 soon, would that solve the issue?  Disabling the cdc prior to index maintenance and then re-enabling back upon completion; would delete the data as I found in most discussions, but we would like to retain it. 

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved