Encryption and the client

I have a basic understanding of the encryption using T-SQL, but is all of this at the T-SQL level? Like can a client have a certificate, be sent the encrypted value (that was encrypted on SQL Server), and using the same cert that is on the server decrypt the data?

Is there a good article/book that covered encryption in detail that you can suggest? Or is this something that you would build in .NET?

If your concern is to protect data in transit, I would strongly recommend you to use SSL to protect the data between your server and the client.

If your goal is to protect data at rest, but in such a way that the protected data cannot be decrypted by the server (i.e. the decryption key is never stored/used in the server hosting SQL Server) you can use .Net to protect the data directly, but all the key management should be on your client application.

Typically, for normal data I don’t recommend using a model where you use public keys to protect data directly on SQL Server, and use the private key on the client to decrypt it (or vice versa).

The main reasons are that your application will still have to do most of the key management (private/public keys), the performance of asymmetric key encryption is orders of magnitude slower than symmetric key encryption and that in SQL Server the limit for encrypting data using an asymmetric key is 1 block (based on the private key modulus, for the self signed certificates SQL Server generates this means you can only encrypt up to ~117 bytes of plaintext).

After saying that, there may be some cases where this mechanism may actually work better for your particular needs. You can find one sample on how to use the .Net framework to encrypt data and decrypt it on SQL Server using asymmetric keys: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384472&SiteID=1

I hope this information can help you.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Actually my question was largely academic in nature, as I was trying to get my head straight on a few topics with SQL Server for a client. I had a few preconcieved notions about how things worked with encryption.

Thanks again (you have helped me before :)!

encrypte strings and numbers

i need a function for encrypte data in t-sql.any idea?
Regards,
OfirSQL does not have an inbuilt encryption function, except the one used for
passwords... If you search for Encrypt SQL Data on the web you will get many
results... Some indiviuduals have come up with simple encryption Sps, etc...
Here is a URL for a 3rd party product..
http://www.activecrypt.com/
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ofir" <ofir@.millenium.org.il> wrote in message
news:uTAz%231QZFHA.3628@.TK2MSFTNGP09.phx.gbl...
>i need a function for encrypte data in t-sql.any idea?
> Regards,
> Ofir
>

enable TCPIP Protocols by script

How do you enable TCPIP in Protocols for Network Config for the sql server
2005 and for all the specific IP addressses via T-SQL script.> How do you enable TCPIP in Protocols for Network Config for the sql server
> 2005 and for all the specific IP addressses via T-SQL script.
I do not know for T-SQL, but I guess you can do it with SMO or WMI. Here is
a VBScript script that uses WMI to enlist the protoclos and enable named
Pipes:
' enum protocols and show status
set wmi =
GetObject("WINMGMTS:\\. \root\Microsoft\SqlServer\ComputerManage
ment")
for each prop in wmi.ExecQuery("select * " & _
"from ServerNetworkProtocol " & _
"where InstanceName = 'mssqlserver'")
WScript.Echo prop.ProtocolName & " - " & _
prop.ProtocolDisplayName & " " & _
prop.Enabled
next
' enable named pipes
for each changeprop in wmi.ExecQuery("select * " & _
"from ServerNetworkProtocol " & _
"where InstanceName = 'mssqlserver' and " & _
"ProtocolName = 'Np'")
changeprop.SetEnable()
next
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||BTW, you have to restart the service if you want changes in network
protocols to take effect.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eRGorcHRHHA.412@.TK2MSFTNGP02.phx.gbl...
> I do not know for T-SQL, but I guess you can do it with SMO or WMI. Here
> is a VBScript script that uses WMI to enlist the protoclos and enable
> named Pipes:
> ' enum protocols and show status
> set wmi =
> GetObject("WINMGMTS:\\. \root\Microsoft\SqlServer\ComputerManage
ment")
> for each prop in wmi.ExecQuery("select * " & _
> "from ServerNetworkProtocol " & _
> "where InstanceName = 'mssqlserver'")
> WScript.Echo prop.ProtocolName & " - " & _
> prop.ProtocolDisplayName & " " & _
> prop.Enabled
> next
> ' enable named pipes
> for each changeprop in wmi.ExecQuery("select * " & _
> "from ServerNetworkProtocol " & _
> "where InstanceName = 'mssqlserver' and " & _
> "ProtocolName = 'Np'")
> changeprop.SetEnable()
> next
>
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>|||This does not enable the TCPIP, change the enable setting from NO to YES in
the TCP/IP properties. Do you have to do any update statement in WMI? I
haven't used WMI before. I did stop and restart my sql server.
I changed your code from Np to tcp as I think that was for named pipes not
tcp .
Any help would be gratefully received
thanks
"Dejan Sarka" wrote:

> I do not know for T-SQL, but I guess you can do it with SMO or WMI. Here i
s
> a VBScript script that uses WMI to enlist the protoclos and enable named
> Pipes:
> ' enum protocols and show status
> set wmi =
> GetObject("WINMGMTS:\\. \root\Microsoft\SqlServer\ComputerManage
ment")
> for each prop in wmi.ExecQuery("select * " & _
> "from ServerNetworkProtocol " & _
> "where InstanceName = 'mssqlserver'")
> WScript.Echo prop.ProtocolName & " - " & _
> prop.ProtocolDisplayName & " " & _
> prop.Enabled
> next
> ' enable named pipes
> for each changeprop in wmi.ExecQuery("select * " & _
> "from ServerNetworkProtocol " & _
> "where InstanceName = 'mssqlserver' and " & _
> "ProtocolName = 'Np'")
> changeprop.SetEnable()
> next
>
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>
>

empty files to another files

Hi
Q1: I want to empty file into another file group. Is it possible?
Q2: What command does empty of the files. I do not see any command from
T-SQL doc but I may be wrong.
Q2: Is the table which are getting moved to another files are available for
transaction?
Thanks
MangeshKeep in mind that the primary file in the primary filegroup contains the
system table definitions and can not be moved. Moreover, no matter how many
other files or filegroups you have defined, the primary file can never be
removed from the database definition.
That being said, having multiple files within a filegroup will allow you to
mark any of these secondary files as EMPTYFILE and then have the data moved
to the remaining files within the same filegroup. To get the index data to
move, you could rebuild the indexes defined within the group or the
clustered index to move the data. You could also preform a database shrink
operation that would move data and index pages from one file to the others.
The only way to move index or data files from one filegroup to another, is
to rebuild the index(es) and define them to reside in the new filegroup.
This will only work on the user tables and indexes.
Sincerely,
Anthony Thomas
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:F0A7AF64-0C5C-4AD8-BCAC-8C57B451B7F6@.microsoft.com...
Hi
Q1: I want to empty file into another file group. Is it possible?
Q2: What command does empty of the files. I do not see any command from
T-SQL doc but I may be wrong.
Q2: Is the table which are getting moved to another files are available for
transaction?
Thanks
Mangesh