I have programs written in sas. The programs themselves can be treated like a text file for this purpose. I need a program that will encrypt my program then when needed decyrypt it storing it into a temp file, run it and then delete it.
any thoughts or anything out there to use.
Nicole
I want to encrypt some audio files before inserting them in a SQL server database. I want to use the built in sql server encryption. I found that the encrypt by key function accepts only text (nchar,nvarchar,etc).
I was thinking or reading the audio file's bytes and change them to a string and then insert them in the database. I just wanted to take opinions here. Is there a better way to do this?
thanks in advance
Hi All,
I have the following requirement. I need to store the password for the connection manager in the configuration file. The sink for the configuration file is SQL Server. Though the password field appears as "******" the actual value is being taken as ""******" itself. If i update the SQL server table with the correct value, then the package starts working. But, the password is shown as clear text.
If i write logic to encrypt the password column in the configuration table, is there a way to tell the SSIS execute engine to decrypt the password before using the same for making the connection.
Is there a place holder, where i can write the decrypt code so that the decrypted password can be sent to the execution engine?
Thanks In Advance,
Madhu
Hi,
I am looking for a way to combine two text files into one file. I am thinking of using a batch file (DOS command ) to do it. Any suggestion please?
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
Hey All,
Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.
I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:
Example of Data
0124015Apple Crate 32.12
0124016Bananna Box 12.56
0124017Mango Carton 15.98
0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money
How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?
I don't know if it makes any difference, but I've been calling SQL from my code by doing this:
Code in C# Form
SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;
SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.
Thanks for your time,
Hayden.
What is the easiest way to accomplish this task with SSIS?
Basically I have a stored procedure that unions multiple queries between databases. I need to be able to export this to a text file on a daily basis and add a total records: row to the end of the text file.
Thanks in advance for any help.
Hello Experts,
I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle).
PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not..
What should go here ?
->Under Class A
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
//Some code to read file and write it into new file
return DTSExecResult.Success;
}
public const string Property_Task = "CustomErrorControl";
public const string Property_SourceConnection = "SourceConnection";
public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)
{
if (node.Name != Property_Task)
{
throw new Exception(String.Format("Invalid task element '{0}' in LoadFromXML.", node.Name));
}
else
{
try
{
_sourceConnectionId = node.Attributes.GetNamedItem(Property_SourceConnection).Value;
}
catch (Exception ex)
{
infoEvents.FireError(0, "LoadFromXML", ex.Message, "", 0);
}
}
}
public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)
{
try
{
// // Create Task Element
XmlElement taskElement = doc.CreateElement("", Property_Task, "");
doc.AppendChild(taskElement);
// // Save source FileConnection
XmlAttribute sourcefileAttribute = doc.CreateAttribute(Property_SourceConnection);
sourcefileAttribute.Value = _sourceConnectionId;
taskElement.Attributes.Append(sourcefileAttribute);
}
catch (Exception ex)
{
infoEvents.FireError(0, "SaveXML", ex.Message, "", 0);
}
}
In UI Class there is OK Click event.
private void btnOK_Click(object sender, EventArgs e)
{
try
{
_taskHost.Properties[CustomErrorControl.Property_SourceConnection].SetValue(_taskHost, propertyGrid1.Text);
btnOK.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
#endregion
}
Hi,
I am pulling text files in gzip format from UNIX system. I want to unzip these files and then import data from these files into database using SSIS.
I have a sql 2005 DB that stores personal information including Social Security #. How can i encrypt the SSN in Sql and then Decrypt it in a asp.net 2.0 page?Thanks
View 3 Replies View Related
I am calling my rports from the server useing URL method.I need to encript this URL please help me.
I have an application that has stores sensitive data in an SQL server and I am currently handling this through my ASP.NET application using the encryption classes in C#.
One of the things we would eventually like to be able to do is use other programs (like Microsoft Access) to run advanced Querys on the tables and retrive the data. With the encyption being done in C#/ASP all that would be returned would be the encrypted data.
I wondering if there is a way to build a layer to encrypt/decrypt data at the database level, my searches haven't yeiled to much info (As well as a trip to Borders) as I haven't seen any books that even touch on this.
I don't know a lot about SQL right now (Mainly only MySQL so Stored Procs and all that stuff are really new to me) so I don't know if I would go about it this way or not? (Using a stored procedure)
Can anyone reccommened where I should start to learn about accomplishing this? Books are usually the best help but I'll take any kind of information that can be thrown my way :-)
Thanks!
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 RelatedHi all,
Can anyone help me regarding encrypting a password.
While i put my password in a table, it shd be encrypted form so that none of the user shd able
view that and for admin purpose i shd able to view, that is, i shd able to decrypt the password
show to the user. The password is stored in a table as a field.
waiting for ur replies.
thanks in advance.
regards,
krr
Is there any way by which a SQL Server database can be encrypted.
Regrads
Hi:
I was wondering if any of you had any idea about how I could achieve to encrypt a certain field(s) or a whole table in one of the table in my Database. Somewhat, I want to hide the password field from everyone's curious eye.
IF possible please also reply to this email :
alexiscloutier@digisoftcorp.com
Hi,We want to encrypt MS Sql Server data files - .mdf and .ldf withlogged in user certificate and make sure that MS Sql Server service(running as Local System Account) can decrypt it.Is it possible to encrypt data files with a certificate that residesin logged in user'scert store and also MS SQL Server Service 'service account's certstore?You can access 'service account's cert store through mmc -
Quote:
Hello,I am running Microsoft SQL Server 2000 on a Windows 2000 Sever. I havebeen working with SQL Server, Building ASp WebSites for many years now.I am by no means an expert - nor have I had ANY formal training. So ebarwith me if my questions seem elementary...I have some questions regarding sensitive data and encryption.There is a project that is headed my way were the social security numberis being used as the unique identifier for an account. I have alwaysused as identity column as a unique identifier. What would be the prosand cons of using the SSN as a unique identifier?#1 How do I go about encrypting the number to store in the DB. Is thisdone within SQL Server? Or before the data is inserted?#2 Is it possible to use an encrypted field as a unique identifier?My gut tells me to use the identity column , encrypt the SSN and not useit as any part of an identifier.Thank You for your Help.. Happy New Year!Please Reply to the Newsgroup.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 12 Replies View RelatedA friend of my self asked me how he can save a password not as clear text. He wanted to encrypt the password and save the encrypted string in the database.
How can he do this? Maybe somebody can help me here.
Regards Markus
Hi,
I have creating a stored procedure in sql server 2005 to encrypt a parameter value that comes from an asp.net page. this value is of type varchar.
Now i have declared a paramater as varbinary to accept this value in the stored procedure. And encrypting it using symmetric key which is encrypted by certificate.
Now when i enter a value into the textbox in asp.net page. it shows
"implicit conversion from varchar to varbinary not possible" pls tell me what is the solution? do i have to convert the value in vb code while accessing the value of the textbox or do i have to convert the value while passing the value when call stored procedure.
pls tell me the solution so that i don't jave to change any coding in vb.
some solution in sql server itselt.
Thank you
Gaurav
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.
I have a bunch of packages that I was working with on a development server and have now moved to a production server. The package protection level was set to DoNotSaveSensative during the development, when I loaded them onto the production server I changed the protection level to EncryptAllWithPassword. I have a few parent packages that call other packages using the execute package task. Since, I now have encrypted packages i had to supply this task with the password, that is where the problem started. The parent package fails when it tries to execute the child package, saying the password was not supplied or it is wrong. I assure you it is not wrong, so is the parent package not storing the password since it was created with no password? Next I created a new parent package and supplied it with a password to call the child package and it works fine. Do I have to recreate all of my parent packages in this way? Is there a way for the parent package to rebuild itself and store the password? Any help would be appreciated.
Nick
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
Does anyone know where there is a good summary of what you can do to encrypt data going between SQL Server and it's clients, and for that matter any other security things you can do with SQL Server ? I've been asked to write a document on this for someone and quite frankly I've got too much proper work to do...
View 1 Replies View RelatedI 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 RelatedHi,
I usually work with MySQL where it is able tl encrypt a password in a database (for users /clients etc) is there a way to do somthing similar in access.
Thanking you in advance
Oli
Any example on encrypting a SSN Field using TDE?
[URL].....
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 RelatedCREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert
OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key
OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key
What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005
Happy Coding...
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.
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.
I need to find a way of encrypting and decrypting data from SQL server 2000. I need to do this as transparently as possible, which is why I need to do this if at all possible before my web application encounters the data.I know this is possible without 3rd party applications using SQL server 2005, as I have a working implementation already; however I need to do this with SQL server 2000 as upgrading is not an option. Using a 3rd party product to encrypt/decrypt is also not possible.Any help much appreciated.Matt Rose
View 4 Replies View RelatedWe are looking at setting up peer-to-peer transactional replication between two databases. We have a customer requirement to encrypt the SSN in this database. I have configured replication successfully. I have also successfully encrypted the SSN using a symmetric key (with encryption by certificate). What I haven't done yet is set up encryption to work across a replication topology.
What steps would I have to follow in order to be able to encrypt the SSN on one server, replicate it to the subscriber, and then decrypt the SSN on the subscriber? For this scenario, is there a better way to handle encryption other than a symmetric key encrypted by a certificate?