I am in the stage of design for an application that uses SQL server 2005. We intended to encrypt some sensitve data using the encryption features in SQL server 2005. we will use symmetric key encryption. The question here is which symmetric encryption algorithm has the best performance? how much does the key size affect the perfromance? the data to be encrypted will be some lines of text equal to a word document.
any ideas?
Hi,
In the info for SQL Compact Edition 3.5 it states that one of the features is:
Support for newer and more secure encryption algorithms.
I can't seem to find details of exactly what these new, more secure algorithms are. It appears 3.1 used 128-bit RSA. Is this the same in 3.5, or has this changed?
Thanks
Hi, all,
Thanks for your kind attention.
Just wonder is there any good idea for us to select attributes for training models? Both for non-supervised algorithms like Association Rules and Clustering etc. and supervised algorithms like decision tree etc.
It will be much interesting to hear from you for any best practices and popular methods of dealing with this issue.
I am looking forward to hearing from you and thanks for your advices.
With best regards,
Yours sincerely,
Hi
I am trying to encrypt data using a symmetric key which is encrypted by certificate. I do not want grant control on these objects to the users who wants to decrypt this data. Instead I have created a udf with execute context as "dbo" and used DecryptByKeyAutoCert built-in function.
Now this works fine but large data operations this is extremely slow. It takes around 10 minutes to select decrypted data whic in comparision takes 11 seconds when DecryptByKey function is used.
But I am not sure when DecryptByKey is used, whether the symmetric key is decrypted by the private key of the certificate or not. Can somebody give some explanation of this ?
Also, I can not have a UDF with these following steps
1. Open symmetric key
2. Convert secretdata using DecryptByKey
3. Close Symmetric Key.
4. return decrypted value.
Can some one give some insights on this ?
I am trying to implement encryption but have run into some serious performance issues. I am required to encrypt the SSN in our database. In and of itself, this is not a problem. The problem comes in because there is also a need to be able to query the table based on the SSN. Since the SSN is encrypted, the query basically performs an index scan, decrypting each value as it goes along. As a result, the query for one record out of 10 million records in the table takes three minutes. It needs to occur immediately.
If I could encrypt my SSN parameter and then compare it to the encrypted value in the column, it would work fine. Unfortunately, everytime you encrypt a particular value, the resultant encrypted value is different. Hence, I have to decrypt the column to match my parameter, instead of encrypting th parameter to match the column.
Does anyone have any suggestions to help alleviate this problem?
Thanks,
Hello All,
Here is the SQL 2005 encryption environment:
1. Clustered SQL 2005 (enterprise edition) on windows 2003. HP (quad processor) with CPU affinity set to all processors.
2. Table structure where encrypted data will be stored has two varbinary (max) columns to store encrypted data. The columns are varbinary (max) b/c the data size could be more that 8K.
3. Encryption using AES (tried both 128/256) algorithm with symmetric keys.
When inserting data in the columns, CPU is staying at 50% when inserting records. Any ideas why this would be happening. Any suggestions on improving performance is appreciated..
Thanks..
Does anyone have any specific performance information using AES or other encryption schemes supported by SQL Server 2005?
- What method did you find works best?
- Did you encrypt any XML Data?
- Did you encrypt specific columns or whole tables?
Thanks,
Michael
Here is how you get the check digit for EAN-8, EAN-13 and EAN-14.CREATE FUNCTION dbo.fnGetEAN
(
@EAN VARCHAR(13)
)
RETURNS VARCHAR(14)
AS
BEGIN
DECLARE@Index TINYINT,
@Multiplier TINYINT,
@Sum TINYINT
SELECT@Index = LEN(@EAN),
@Multiplier = 3,
@Sum = 0
WHILE @Index > 0
SELECT@Sum = @Sum + @Multiplier * CAST(SUBSTRING(@EAN, @Index, 1) AS TINYINT),
@Multiplier = 4 - @Multiplier,
@Index = @Index - 1
RETURNCASE @Sum % 10
WHEN 0 THEN @EAN + '0'
ELSE @EAN + CAST(10 - @Sum % 10 AS CHAR(1))
END
END
E 12°55'05.25"
N 56°04'39.16"
I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.
best way to encrypt and decrypt using user defined functions.Below is the query which is taking long time.
SELECT ID FROM table WITH (NOLOCK)
WHERE dbo.DecodeFunction(column) = 'value'
When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.
Here is how you get the check digit for ISBN.CREATE FUNCTION dbo.fnGetISBN
(
@ISBN VARCHAR(11)
)
RETURNS VARCHAR(13)
AS
BEGIN
DECLARE@Index TINYINT,
@Weight TINYINT,
@Sum SMALLINT
SELECT@Index = LEN(@ISBN),
@Weight = 2,
@Sum = 0
WHILE @Index > 0
BEGIN
IF SUBSTRING(@ISBN, @Index, 1) <> '-'
SELECT@Sum = @Sum + @Weight * CAST(SUBSTRING(@ISBN, @Index, 1) AS TINYINT),
@Weight = @Weight + 1
SET @Index = @Index - 1
END
RETURNCASE @Sum % 11
WHEN 1 THEN @ISBN + '-X'
ELSE @ISBN + '-' + CONVERT(CHAR(1), 11 - (@Sum % 11))
END
END
E 12°55'05.25"
N 56°04'39.16"
I just want to know what are the data algorithms available for sql server 2005 and can i use data mining as a web service
Hi All,
We're currently preparing for a project for a bank client of ours where we would be using SQL Server 2008's data mining capabilities.
In the context of the out-of-the-box algorithms of SQL08 does the algorithms Logistics, Clustering and Logistic Regression includes Dynamic Logistics, Dynamic Clustering and Dynamic Logistic Regression?
Regards,
Joseph
Hello Everyone,
I am reading DataMining Tutorial and right now I am at the Mining Algorithms section. I cannot understand any of the algorithms. For example take the following text... what a bunch of mouthful bla bla bla it is ....
"The Microsoft Decision Trees algorithm supports both classification and regression and it works well for predictive modeling. Using the algorithm, you can predict both discrete and continuous attributes.
In building a model, the algorithm examines how each input attribute in the dataset affects the result of the predicted attribute, and then it uses the input attributes with the strongest relationship to create a series of splits, called nodes. As new nodes are added to the model, a tree structure begins to form. The top node of the tree describes the breakdown of the predicted attribute over the overall population. Each additional node is created based on the distribution of states of the predicted attribute as compared to the input attributes. If an input attribute is seen to cause the predicted attribute to favor one state over another, a new node is added to the model. The model continues to grow until none of the remaining attributes create a split that provides an improved prediction over the existing node. The model seeks to find a combination of attributes and their states that creates a disproportionate distribution of states in the predicted attribute, therefore allowing you to predict the outcome of the predicted attribute"
In the above text what is meant by discrete and continious attributes? what is regression? what is predicted attributes? what are input attributes? what is distribution of states?
Is there a source which explains these algorighms in a easier way ....
hi,
Is it possible to use two algorithms together?I need to write prediction Query so that its should both models having clustereing algorithm and timeseries algorithm.
for example
I am having student information.I ve to predict performance of students for certain period.The students should be classified by their types like rich kids,poorkids..like that.I need to predict the performance of the rich kids??
can anyone help?
Can anybody answer this question ?
I was just wondering on what algorithms was used in the different feature buttons in the Excel Add-In? For example, forecast, what algorithm was used in order for it to create the forcasting?
Hi, all here,
I am having a question about plug-in algorithms in SQL Server 2005. Since we are able to implement our own algorithms in SQL Server 2005 analysis services architecture, so my question is: what benefits can to a great extent be achieved? Like say, we are going to implement a plug-in algorithm, so what considerations should be concerned?
Thanks a lot in advance for any guidance and help.
Hi, all here,
I am having a more considertaion about Data Mining plug-in algorithms. When we say we are going to embed a uesr plug-in algorithm, so what is the context for that ? I mean in which case then we thing we need to embed a user plug-in algortihm? I know when we say we are going to embed a user costomermized plug-in algorithm, it means we want something more costomized. But what kind of customized features are generally concerned? Is it independant for different market sectors?
I dont think we can just try to embed a plug-in algorithm then compete it with avaialble algorithms to see which one is with better prediction accuracy?
Would please someone here give me some guidances about that?
Hi,
I recently started using SQL 2000 Analysis Manager. I wanted to try data mining but was unable to get the Mining Model Wizard to load available techniques.
When I select a cube and "New Mining Model" I get the following error:
"Unable to get list of data mining algorithms."
"Object of provider is not capable of performing requested operation"
Please help.
Thank you very much,
Robert
Hi, all experts here,
Thank you for your kind attention.
Could please any of you give me some advices for if there are tutorials and demos avaiable which cover all the SQL Server 2005 data mining built-in algorithms?
That will be great to hear from any of you shortly. Thanks a lot in advance.
With best regards,
Yours sincerely,
1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View RelatedHello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
View 2 Replies View RelatedI was wondering if anyone out there knows if it is possible to encrypt a particular field in a table, or encrypt a whole table. The info would remain on my database and not be sent out anywhere, but I just want an extra level of security against anyone who might try to break into the database.
View 2 Replies View RelatedIs there a function that can encryp the data in a table(or certain column)?
So if the table or column was query the person would see something like " !#)&%^#@ ". suggestion are welcome.
Kevin
Hello,
Is there a way to encrypt the data ( I mean actual data stored
in a table)in a SQLServer.
I know how to encrypt procedures, views, Net-libraries ......
Please help!!!!
Thanks.
How to get the encryption of certain characters, such as '12345' or 'hello'. Is any function to take regular characters and return the encryption form of those characters?
Please help.
My client requested that the password field of a login table be encrypted using H5 Encryption. I've been searching throughout the net and MSDN for the function or procedure and can't find it. Does anybody know how?
View 1 Replies View RelatedI developed a small VB6 application VB6 that get data from SAP sql database
Now they want that the users logon to this small app using the same username & password that they used in SAP
I can read the users' table, I can get the username
But the password is encrypted
Probably with a Function
Anybody know how can I encrypt the typed password so I can compare with the saved password
I don't want to view or modify them, just compare with the password typed by the user
Thanks
JG
Hello,
I am needing to migrate an Access database to Sql Server
Express. This database will be distributed as part of a
VB 6 application. This database will have some of the
columns encrypted. My understanding is that SQL Server
Express supports encryption. We will need to be able to
run queries on encrypted data like this, with
LastName being an encrypted column in the database.
Select LastName from Account where LastName = 'Smith'
Select LastName from Account where LastName Like 'Sm%'
Update Account Set where LastName = sLastName
Can this be done using SQL Server 2005 Express and VB 6?
Thanks!
hi all,
ive download some application, but they encrypt their Stored Proc and some Views..
1.how to encrypt SP?
2.is it possible to decrypt whateva that encrypted?
~~~Focus on problem, not solution~~~
Hai
I am new to SQL server and working on Encryption.I just want to know whether it is better to encrpt a data in database or in the code itself.I want to encrypt a number and not a text.which one would u suggest RC4 algorithm or pwdencrypt..any one plz reply soon..
Hi. Im new to SQLserver. I need to encrypt a column of datatype decimal. Which is better RC4 Algorithm or PWDencrypt() and PWDcompare().
Sundaresan.R