Anonymousdog
I'm building a hosted website and I am using SQL 2005.
The DBA for the host has told me that i can not encrypt a symmetric key with a certificate, when using that symmetric key for encryption. As i read that this method provided optimum performance/ security for encrypting columns of data.
The DBA told me i can use a cert or a symmetric key for encryption.
I have searched for comparisons and found a blog entry by Laurentiu Cristofor comparing certs with asymmetric keys. Which leads me to believe that certs and asymm are very different than symmetric keys.
My question is which is the best choice in a hosted environment for column encryption, a cert or symmetric key.
Which is more secure Does one offer a significant performance (dis)advantage
TIA
Encryption Choices / Best Practices for hosted environment (shared server)
SQL Server Security
-
- Previous
- 1
- task a trigger Hi, How do I make a trigger be fired automatically on a daily basis in a specific hour Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 2
- script to populate pre defined cell in excel using ssis ? How to populate an excel cell from a sql table using ssis. I need to populate a specified cell lets say H7, in an excel sheet, from a sql table in a ssis package. Suppose we have a column in sql table called total, which contains some value, we need to take this value and populate the cell H7 in an excel sheet. Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 3
- SP2 redistributable size SQL Server 2005 Express (no sp) is 53.5 MB http://www.microsoft.com/downloads/details.aspx familyid=220549b5-0b07-4448-8848-dcc397514b41&displaylang=en SQL Server 2005 Express SP2 is 36.5 MB http://msdn.microsoft.com/vstudio/express/sql/download/ That's a diff in size of almost 20 MB. How can the SP2 be so much smaller Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 4
- Design Question- Dynamic Configuration of Metadata in Dataflow Hey Jess, I'm impressed. This is the first time I've seen a working bit of code that does this. Do you mind if I put a link from my blog to this I think this needs to be shared. One thing, the correct nomenclature is a custom task, not a custom component. My god, I must be in a critical mood Regards -Jamie Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 5
- Connection Manager Questino I know this is a dumb question, but what exactly does connection manager do in SQL Server 2005 If it works like I think it does, then it sets up BI packages on the servers that it creates a connection to. However, if my computer goes down, are these packages lost to the server -Thank you in advance. Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 6
- Help Needed ! , Data Migration from MS Access2003 to SQL Express 2005 Hi , I have a requirement to migrate the data from an existing MS Access database to a newly designed SQL Express 2005 database . Need less to say the table structures in both are totally different.I would like to know how can i handle a scenerio where i want to map table A in access to table B in SQL express (the schema of both different and the number of columns can vary too) , how do i migrate the data from table A in Access to Table B in SQL express using SSMA Also i would appreciate if some one can tell me is SSMA the right tool for this , or should i use the upsizing wizard of MS Access.If there is no change in schema between source and destination databases (more of upsizing) then the process is pretty straight forward , The constraint here is that the data needs to be migrated to a new schema where the column names and number of columns can vary between the source table and destination table.. I just need to migrate data only and no other objects. Need Help! Thanks Mahesh Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 7
- An error occurred while receiving data: '64(error not found)' We are using Service Broker to synchronise two databases using async triggers (using a middle server to preform data mapping processing). We are re-using dialogs as we want to ensure order when sending messages which contain the data to be synced. A dialog is created between each intiator and target service (there are 3 of each) which is kept open indefinately (we are only ending conversation upon receiving an end conversation message or error message). We seem to get it working for a period then after about 1 hour it seems to stop sending and we see the following error in SQL Profiler: An error occurred while receiving data : '64( error not found )' Any ideas what could be causing this We do not see any errors or end conversations. It seems to happen at irregular points. Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 8
- Data Not Copying to MS Access Table Bob Bojanic - MSFT wrote: Seaarch this forum to get instructions for making sure the packages are executed in 32-bit mode. Thanks, Bob I went to the package properties, debugging, and set Run64BitRuntime to False. I still get the same result The data will preview, but will not transfer into the Access table. In the Data Flow Task, I'm using an OLEDB source to execute the sql statement. Should I be using an Execute SQL Task instead The sql statement also uses a TABLE variable. Could this be a problem Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 9
- 64 bit Oracle connection John is correct about the flag determining which provider (32bit or 64bit) will be used. You have to call the correct executable. 32-bit example: Change the step type to : Operating system (CmdExec) In the command window you would something similar to what I have below but substitute your package name: "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /DTS "\MSDB\ AMPFMDATALOAD " /SERVER URHCS63 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 10
- Disabling a task at runtime This has been discussed a few times before. If you search the forums, you'll see some of the background on it. You can disable a task at runtime using expressions, but once it is disabled, it never checks to see if it should be reenabled. Personally, I think it is a bit of a bug, but I understand why it works that way. Once a task is disabled, the runtime ignores it. Expressions aren't updated on it. Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 11
- Combining the content of two tables Hi all, How can I combine the contents of the two tables below The combination result of these tables is provided below. Thanks Table A Client Weight Purchase Tom 10 2 Bill 4 2 John 3 2 Table B Client Weight Purchase Jim 2 5 Lee 4 3 Bob 6 7 Combination table (result) Client Weight Purchase Tom 10 2 Bill 4 2 John 3 2 Jim 2 5 Lee 4 3 Bob 6 7 Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 12
- Query to obtain users and privileges from databases Hi to all, is my first post, i need a query or script to obtain all users and privileges from all my databases, someone to help me. I'm learning Administration SQL server 2005. I know that sys.database_principals and sys.server_principals have information about that, but i need users - privileges of every database. thank you Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 13
- SQL Enterprise Manager I administer a website that is database driven on SQL. for the past few months i have been using Access project to edit my tables. I am switching to a host that does not support that and says I need SQL Enterprise Manager to work with my data. Does anyone have any simple ideas on what my next step should be any suggestions would be appreciated. Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 14
- 2005 Transactional Replication Performance Problems - help please try to find a specifc cumulative hotfix to solve you problem. Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 15
- Problems while attaching a database in SQLServer 2000 I recive a error when i attaching a database in sqlserver 2000. The error is :- An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs. Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602) Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 16
- Urgt Pls:Cell merge prob Ok..thats nice to hear..Good Luck!! Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 1
- Next
- 1
- Remote access to sql server To access SQL Server from a remote machine two conditions must be met: (1) SQL Server accepts remote connections. For SQL Server 2005 you can use the Surface Area Configuration tool to ensure this, see http://msdn2.microsoft.com/en-us/library/ms178763.aspx . (2) Firewall must not block SQL Server. If you are using Windows firewall on the SQL Server machine the easiest way is to grant an exception for the SQL Server executable process (by default in a location like C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe). Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 2
- Table and View permissions Do you see what I see , It's not my fault Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 3
- How to develope a notification service application hi all, i am a novice in notification service.i am trying to develope an application on notification service but i am not able to do a sample application.i am not able to understand what is instance and rest of the things in notification service. can anybody provide any guideline or links so that i am able to understand what notification service is and how to create a sample application. please help thanks a lot in advance. Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 4
- How to start with historization of dimensions!? Hi there, I've got a question regarding the historization of dimension tables. How do I approach it at best Let's say I've got 4 dimensions I want to apply historization to (article, customer, agent, forwarding). What is the starting point in Analysis Services Things that aren't clear to me: 1. If I use snapshot for historization I think all of the tables need to have 3 additional columns added (at the moment I've just got data-table without time stamps). Let's take an exemplary look at the article table: Code Snippet artno artgroup comgroup brand description 4711 Orange Juice Fresh TropicalWonder So, If I am correct I need to add these rows in order to guarantee a historization: Code Snippet artno valid from valid to last refresh 4711 2005-01-01 2005-12-31 2006-01-01 4711 2006-01-01 2099-12-31 2006-01-01 2. If this is correct, my second problem is, how do generate these informations 3. And last but not least, how does a cube draw a distinction between the two values As the fact table is just connected to the dimension via the artno... Thanks in advance! Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 5
- How to create an excel connection manager programmatically ? Thanks for the reply, Darren. The first SetComponentProperty worked, but not the second, which gets a runtime exception. Is the property under a different name Thanks for your help! ' Create and configure an OLE DB destination. Dim conDest As ConnectionManager = package.Connections.Add( "Excel" ) conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ Dts.Variables( "User::gsExcelFile" ).Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES""" conDest.Name = "Excel File" conDest.Description = "Excel File" Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New destination.ComponentClassID = "DTSAdapter.ExcelDestination" ' Create the design-time instance of the destination. Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate ' The ProvideComponentProperties method creates a default input. destDesignTime.ProvideComponentProperties() destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest) destDesignTime.SetComponentProperty( "AccessMode" , 0) 'runtime Exception here destDesignTime.SetComponentProperty( "OpenRowSet" , "functions" ) Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 6
- SQL Server 2000 Service Pack 4 won't install I appreciate your help so far, but I can't help but be disappointed that your suggestion is to use the NT Authority/Network Service user to run my SQL Server 2000 services. As far as I've read, that is not a best practice recommended by Microsoft. Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 7
- Granting UPDATE for only certain columns in a table Very nice. I have no answer as to why this is, but I will check around and let you know. Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 8
- Remove time part In the report field you can do the following: =LEFT(Fields!Admit_Date.Value,11) and it will only display 11/15/2007 Or you can do the following in the data source and not need the above: Select LEFT(srm.Patient_Visit.Admit_Date,11) FROM srm.Patient_Visit Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 9
- Unable to completely uninstall Advanced SQL Server- Report Server 1) I uninstalled Advanced SQL with Report Server to install 2005 SQL Developers Editions. 2) For some reason I am unable to completely uninstall Express Report Server. 3) Configuration Manager is still showing SQL Exp Report Server, even though I uninstalled Advanced Server. 4)I tried to remove though A/R programs (using my 2005 DE) - I see it listed but I receive the following error:The setup failed to read IISmime maptable the error code is -2147024893. 5) When I try to use 2005 Report Service Config Manager, I receive the following error: An unknown error in WMI Provider Error code 80040219 wmi provider. So I am unable to setup my 2005 SQL DE Report Server. Any ideas on uninstalling Express Report Server...This might be part of my problem. Thanks Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 10
- CREATE SCHEMA fails Inside an If Block This is such an OBVIOUS shortcoming of TransactSQL. Why hasn't Microsoft corrected this flaw. As far as I know (with the exception of entering the exec ('sneak the CREATE in as a text string') there is no way to conditionally create a SCHEMA or a FUNCTION for that matter. If it's disallowed for security reasons then why can we sneak it in with an EXEC The problem this causes for me (over and over again) is I write a sample function for the user and include it in my upgrade script. If they've already run the script for an earlier version, they already have my example and may have customized it to their specific application. If they have, I don't really want to replace it with my example again. So I'm stuck sneaking it in through the string route. This has been a flaw in T-SQL for a long time.... Sorry for the rant... Sure wish the T-SQL gods were listening Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 11
- How to enable remote connections by code? How to enable remote connections by code without "SQL Server Surface Area Configuration" ,implement by .net framework Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 12
- How to do SUM of Average in subtotal cell for the entire matrix What you are looking to accomplish requires nested aggregates, the average of a subtotal. Nested aggregates are not currently supported. There is a workaround, however. The workaround is to use a custom function that uses the appropriate expression, based on the current scope. In this example, there are four different scopes in which the cell is calculated. One for the cell, the subtotal, average, and the average of the subtotals. The custom function for the first two cases simply returns the aggregate value passed in. The third case will take the subtotal passed in and add it to a running total, as well as keep a count values added to the total, and then return the subtotal. The fourth case will calculate and return the average, and reset the running total and count. The custom function is called using the following expression, assuming that the column group is named "Date" and the inner row grouping is named "Division." =Code.CalculateSumSubtotalOrAverage(Sum(Fields!FieldName.Value),AVG(Fields!FieldName.Value), InScope("Date"), InScope("Division")) Here is the custom function. Code Snippet Private m_total As Double Private m_count As Integer Public Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double If inDateScope And inDivisionScope Then ' Regular cell Return subtotal Else If Not inDateScope And inDivisionScope Then ' Average of Division Return average Else If inDateScope And Not inDivisionScope Then ' Subtotal of Date m_total = m_total + subtotal m_count = m_count + 1 Return subtotal Else ' Average of Subtotal Dim avg as Double avg = m_total / m_count m_count = 0 m_total = 0 Return avg End If End Function Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 13
- Doing a keyword search inside the contents of the stored procedures of a database another question pls: I wanna customize the code: SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%mySearchedKeyword%' to search all databases and not only the current one. Thanks. Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 14
- processing cube not pumping data 1. I did not use tables or views because fact set needs to be produced by a join of 3 tables. 2. The same cube (with same named queries) work well if I add a clause to the Fact (named query) which limits the output rows. When I remove the filtering condition it causes the processing to not pump data and flood tempdb. FYI when I specify the filtering condition I still process about 10M fact rows. 3. All keys and relationship seem to work with a smaller set of data - 10M Fact Rows. (The full set of Fact data is about 630M rows). 4. The dimension usage appears to be correct in BIDS. Also when I process the cube with filtered down fact set (to 10M rows), i am able to connect to cube and browse it in BIDS and it seems to be all good. 5. I had seen missing key value error as you describe but that only happens when it actually pumps fact data and then errors out of the processing. I have seen these errors on filtered fact dataset (10M rows) and eliminted those errors - also reprocessed those dimensions. SQL Server seems to behave different when the same query is sent from Analysis services vs. when it's run from SQL Management Studio. I pasted the query from Analysis Services processing cube progress window and ran it just find in the SQL Management Studio - starts pumping data within 1-2 seconds and does not flood tempdb. Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 15
- Why is the index not being used? The crossover was it. Wow. I changed the -15 to -13 and it started using the index. Thanks for the prompt response. M Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 16
- SQL Server database disappearing! Hi, We have a number of databases on our SQL Server 2005. One of them, AddressPoint, keeps disappearing completely every few days. I should qualify that - the database name still exists and is visible in Management Studio, but there is no + icon to expand to see the object types. I can manually restore the database. But we are unable to identify any process that might be deleting the database. So where do I even start in trying to track down the process that is deleting it I have not been able to pinpoint a specific time when the deletion might have occurred, as I am not using the database regularly, and neither are our users, yet. The latest deletion definitely occurred within the last 24 hours, but that's as specific as I can be. Thanks, Keith. Tag: Encryption Choices / Best Practices for hosted environment (shared server)
- 1
- Previous
Re: Encryption Choices / Best Practices for hosted environment (shared server)
Steven Gott - MS
I'd encrypt the column data with a symmetric key and protect the symmetric key with an asymmetric key or a cert.
The encryption / decryption operations with a symmetric key are much faster then the same operations with an asymmetric key or cert.
I'd use AES_128 | AES_192 | AES_256 for the algorithm if the hosted OS supported it.
HTH,
-Steven Gott
SDE/T
SQL Server
Re: Encryption Choices / Best Practices for hosted environment (shared server)
Anonymousdog
Steve, thanks for helping.
I think i need to clarify my question. On my development machine at home I am currently doing what you reccomend. Ecrypting the symmetric key with a certificate and using the symmetric key to encrypt column data.
BUT, to deploy my database on a shared server (hosted machine) the DBA at the host has told me I am not allowed to do this. I am only allowed to ecnrypt columns directly with a certificate or a symmetric key. (I need to retrieve the column data so I can't do asymmetric)
Is there a security benefit to using a cert over a symmetric key (I am assuming i can directly encrypt data with just a cert.)
Basically what are the pro's and cons of encrypting data directly with only a cert and only a symmetric key.
TIA,
josh
Steven Gott - MS wrote:
I'd encrypt the column data with a symmetric key and protect the symmetric key with an asymmetric key or a cert.
Steve, thanks for helping.
I think i need to clarify my question. On my development machine at home I am currently doing what you reccomend. Ecrypting the symmetric key with a certificate and using the symmetric key to encrypt column data.
BUT, to deploy my database on a shared server (hosted machine) the DBA at the host has told me I am not allowed to do this. I am only allowed to ecnrypt columns directly with a certificate or a symmetric key. (I need to retrieve the column data so I can't do asymmetric)
Is there a security benefit to using a cert over a symmetric key (I am assuming i can directly encrypt data with just a cert.)
Basically what are the pro's and cons of encrypting data directly with only a cert and only a symmetric key.
TIA,
josh
Re: Encryption Choices / Best Practices for hosted environment (shared server)
Steven Gott - MS
The performance of encrypting by certificates will be very bad for large amounts of data. Certificates are better for signing things than encrypting them.
You can also look at Raul's blog for insight into cryptography in SQL Server here is an entry involving indexes and encrypted columns http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx
I'd encrypt with a symmetric key.
HTH,
-Steven
SDE/T
SQL Server
Re: Encryption Choices / Best Practices for hosted environment (shared server)
Laurentiu Cristofor
Does your dba has any rationale for not letting you use a symmetric key encrypted with a certificate That is a best practice for encryption, if there ever was one.
You can also encrypt the symmetric key with a password, instead of using a certificate, but then you'll have to pass that password around, whenever you'll need to open the key.
Certificates are much slower at encryption than symmetric keys and they have some additional limitations on how large a piece of data they can encrypt, hence it's not recommended to use them for encrypting data directly. I second Steven's suggestion to look at Raul's blog for additional details on this.
Thanks
Laurentiu