encryption with certificate

I am trying to create a encrypted row in my database
Everything here worked except that when i run the final query to decrypt the data
It just comes up with null for each row. Even if i do a query to show me the rows that are not null
It's like it is saying yeah there is data here but I am only going to show you null instead of what I am supposed to decrypt.
Here is what I tried from start to finish

Create Certificate TestCertEncryptionBy Password ='Password'With Subject ='SQLCert',Expiry_Date ='12/01/2050';declare @.Testnvarchar(50)set @.Test='123456789'insert into testenc (testencry)Values (encryptbyCert(Cert_ID('TestCert'),@.Test ))selectconvert (Nvarchar(50),DecryptByCert(Cert_ID('TestCert'),testencry,N'Password'))As Testfrom testenc

I am using sql 2005 by the way|||Nevermind Just realized I need to use VarBinary instead of NvarChar to store the data!

Encrypting a Single Row/Column

Hi,
Is there any way to encrypt and decrypt a Single Row/Column in SQL
server 2000 and SQL server 2005.
I do not find any info w.r.t it in the documentation.
Thx in advans,
Karthik BalaguruOn Jul 25, 12:04 pm, karthikbalaguru <karthikbalagur...@.gmail.com>
wrote:
> Hi,
> Is there any way to encrypt and decrypt a Single Row/Column in SQL
> server 2000 and SQL server 2005.
> I do not find any info w.r.t it in the documentation.
> Thx in advans,
> Karthik Balaguru
Hi,
I found the below link having some info w.r.t Encrypt. But it is for
SQL Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
http://groups.google.co.in/group/microsoft.public.sqlserver.programming/browse_thread/thread/fd4a204efd4a69a8/?hl=en#
Thx in advans,
Karthik Balaguru
-->>
You can use the encrypt function even if you don't know what's the
encryption algorithm.
It will encrypt your data using the same algorithm that is used to
encrypt
stored procedures, triggers and views.
create table users(
usernamed varchar(25),
pass varbinary(16)) -- 8 characters password
insert into users values('user1', encrypt('123'))
insert into users values('user2', encrypt('456'))
insert into users values('user3', encrypt('789'))
select * from users
usernamed pass
-- --
user1 0x3E16CDED63C6
user2 0x3B3AB5941B3C
user3 0x383C6F64E6D0
(3 row(s) affected)
select * from users where pass = encrypt('123')
usernamed pass
-- --
user1 0x3E16CDED63C6
(1 row(s) affected)
Remember that encrypt is an undocumented and unsupported function. As
such
data encrypted with the function might become corrupted in a migration
to a
later version of SQL Server.
The encrypt function changed from SQL Server 6.x to
SQL Server 7.0, and individuals that used the encrypt function to
encrypt
data in SQL Server 6.x had such problems.
-->>
I find that the above has some info w.r.t Encrypt. But it is for SQL
Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
Thx in advans,
Karthik Balaguru|||Hi,
Please check:
How to: Encrypt a Column of Data from SQL 2005 BOL
Also:
See the following links for information on how to search encrypted
data:
http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx
http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx
HTH,
Dinesh|||On Jul 25, 12:59 pm, dinu_b...@.hotmail.com wrote:
> Hi,
> Please check:
> How to: Encrypt a Column of Data from SQL 2005 BOL
> Also:
> See the following links for information on how to search encrypted
> data:
> http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspxhttp://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx
> HTH,
> Dinesh
Thx Dinesh, Looks interesting.
Karthik Balaguru

Encrypting a Single Row/Column

Hi,
Is there any way to encrypt and decrypt a Single Row/Column in SQL
server 2000 and SQL server 2005.
I do not find any info w.r.t it in the documentation.
Thx in advans,
Karthik BalaguruOn Jul 25, 12:04 pm, karthikbalaguru <karthikbalagur...@.gmail.com>
wrote:
> Hi,
> Is there any way to encrypt and decrypt a Single Row/Column in SQL
> server 2000 and SQL server 2005.
> I do not find any info w.r.t it in the documentation.
> Thx in advans,
> Karthik Balaguru
Hi,
I found the below link having some info w.r.t Encrypt. But it is for
SQL Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
http://groups.google.co.in/group/mi...d4a69a8/?hl=en#
Thx in advans,
Karthik Balaguru
-->>>>>
You can use the encrypt function even if you don't know what's the
encryption algorithm.
It will encrypt your data using the same algorithm that is used to
encrypt
stored procedures, triggers and views.
create table users(
usernamed varchar(25),
pass varbinary(16)) -- 8 characters password
insert into users values('user1', encrypt('123'))
insert into users values('user2', encrypt('456'))
insert into users values('user3', encrypt('789'))
select * from users
usernamed pass
-- --
user1 0x3E16CDED63C6
user2 0x3B3AB5941B3C
user3 0x383C6F64E6D0
(3 row(s) affected)
select * from users where pass = encrypt('123')
usernamed pass
-- --
user1 0x3E16CDED63C6
(1 row(s) affected)
Remember that encrypt is an undocumented and unsupported function. As
such
data encrypted with the function might become corrupted in a migration
to a
later version of SQL Server.
The encrypt function changed from SQL Server 6.x to
SQL Server 7.0, and individuals that used the encrypt function to
encrypt
data in SQL Server 6.x had such problems.
-->>>
I find that the above has some info w.r.t Encrypt. But it is for SQL
Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
Thx in advans,
Karthik Balaguru|||Hi,
Please check:
How to: Encrypt a Column of Data from SQL 2005 BOL
Also:
See the following links for information on how to search encrypted
data:
http://blogs.msdn.com/lcris/archive.../22/506931.aspx
http://blogs.msdn.com/raulga/archiv.../11/549754.aspx
HTH,
Dinesh|||On Jul 25, 12:59 pm, dinu_b...@.hotmail.com wrote:
> Hi,
> Please check:
> How to: Encrypt a Column of Data from SQL 2005 BOL
> Also:
> See the following links for information on how to search encrypted
> data:
> http://blogs.msdn.com/lcris/archive.../11/549754.aspx
> HTH,
> Dinesh
Thx Dinesh, Looks interesting.
Karthik Balaguru

Encrypting a Single Row/Column

Hi,
Is there any way to encrypt and decrypt a Single Row/Column in SQL
server 2000 and SQL server 2005.
I do not find any info w.r.t it in the documentation.
Thx in advans,
Karthik Balaguru
On Jul 25, 12:04 pm, karthikbalaguru <karthikbalagur...@.gmail.com>
wrote:
> Hi,
> Is there any way to encrypt and decrypt a Single Row/Column in SQL
> server 2000 and SQL server 2005.
> I do not find any info w.r.t it in the documentation.
> Thx in advans,
> Karthik Balaguru
Hi,
I found the below link having some info w.r.t Encrypt. But it is for
SQL Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
http://groups.google.co.in/group/microsoft.public.sqlserver.programming/browse_thread/thread/fd4a204efd4a69a8/?hl=en#
Thx in advans,
Karthik Balaguru
-->>>>>
You can use the encrypt function even if you don't know what's the
encryption algorithm.
It will encrypt your data using the same algorithm that is used to
encrypt
stored procedures, triggers and views.
create table users(
usernamed varchar(25),
pass varbinary(16)) -- 8 characters password
insert into users values('user1', encrypt('123'))
insert into users values('user2', encrypt('456'))
insert into users values('user3', encrypt('789'))
select * from users
usernamed pass
-- --
user1 0x3E16CDED63C6
user2 0x3B3AB5941B3C
user3 0x383C6F64E6D0
(3 row(s) affected)
select * from users where pass = encrypt('123')
usernamed pass
-- --
user1 0x3E16CDED63C6
(1 row(s) affected)
Remember that encrypt is an undocumented and unsupported function. As
such
data encrypted with the function might become corrupted in a migration
to a
later version of SQL Server.
The encrypt function changed from SQL Server 6.x to
SQL Server 7.0, and individuals that used the encrypt function to
encrypt
data in SQL Server 6.x had such problems.
-->>>
I find that the above has some info w.r.t Encrypt. But it is for SQL
Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
Thx in advans,
Karthik Balaguru
|||Hi,
Please check:
How to: Encrypt a Column of Data from SQL 2005 BOL
Also:
See the following links for information on how to search encrypted
data:
http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx
http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx
HTH,
Dinesh
|||On Jul 25, 12:59 pm, dinu_b...@.hotmail.com wrote:
> Hi,
> Please check:
> How to: Encrypt a Column of Data from SQL 2005 BOL
> Also:
> See the following links for information on how to search encrypted
> data:
> http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspxhttp://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx
> HTH,
> Dinesh
Thx Dinesh, Looks interesting.
Karthik Balaguru

Empty table problem

I am creating three tables in a DataSet using three stored procedures and I need a way to know that either the tables have at least one row or preferably which if any has no rows. How can I do that?

using three SP's, one for each table:

SqlCommand cmd =newSqlCommand("CompanyCheck", con);
SqlCommand cmd2 =newSqlCommand("ContractorVerify", con);
SqlCommand cmd3 =newSqlCommand("StoreLocation", con);

cmd.CommandType = CommandType.StoredProcedure;
cmd2.CommandType = CommandType.StoredProcedure;
cmd3.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@.CompanyID", CompanyID);
cmd2.Parameters.AddWithValue("@.CompanyID", CompanyID);
cmd3.Parameters.AddWithValue("@.CompanyID", CompanyID);
SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = cmd;
DataSet ds = new DataSet();
try
{
da.Fill(ds, "CompanyInfo");
da.SelectCommand = cmd2;
da.Fill(ds, "ContractorInfo");
da.SelectCommand = cmd3;
da.Fill(ds, "StoreInfo");
}
catch
{ throw new ApplicationException("Data error"); }
finally
{
con.Close();
}

Thank you

Zoltac,

I would try

if (ds.Tables.Count == 3)
{
if(ds.Tables[0].Rows.Count > 0)
{ }
if(ds.Tables[1].Rows.Count > 0)
{ }
if(ds.Tables[2].Rows.Count > 0)
{ }

Is this what you are looking for? If not, I might be misunderstanding your question.

|||

Thank you - Exactly what I was looking for - maybe one of these days I will finally get it. I need a big wall chart with all the properties and methods, etc,, etc. and maybe then I will have a clue as to what Net 2.0. is made up from. appreciate the quick response.

Empty Subreport in Table

I have a problem with an subreport. I have master-detail relatinship
between tables. My table has three rows, 1st row is table header, 2nd
row is Master data and 3rd row is place where I put mine child
table(subreport).
If my subreport doesnt have any data this row is still visible(empty
space), I search all over internet and didnt find resolve for this
problem. Im working with Visual Studio 20005.
Is there any way for finding out number of rows in subreport, and use
that in Visibility expression of the 3rd row'
Thanks in advance,
Alen Husanovicanyone?|||I am having the exact same problem. Anybody have a proposed solution?
"alenhusanovic@.gmail.com" wrote:
> anyone?
>|||I am, also, having the same problem. Does anyone have a solution or isn't
there one? I would appreciate some kind of a reply.
"bjkaledas" wrote:
> I am having the exact same problem. Anybody have a proposed solution?
> "alenhusanovic@.gmail.com" wrote:
> > anyone?
> >
> >|||And the problem is?
When asking for help you need to include the question. If you make it
difficult to find out the question then you decrease the chance of someone
answering it. In this case I don't have the starting post on this thread so
I don't have a clue what your problem is.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MikeS" <MikeS@.discussions.microsoft.com> wrote in message
news:5015E622-82AA-4446-846A-A5A039891708@.microsoft.com...
>I am, also, having the same problem. Does anyone have a solution or isn't
> there one? I would appreciate some kind of a reply.
> "bjkaledas" wrote:
>> I am having the exact same problem. Anybody have a proposed solution?
>> "alenhusanovic@.gmail.com" wrote:
>> > anyone?
>> >
>> >|||Sorry, here is the starting post:
I have a problem with an subreport. I have master-detail relatinship
between tables. My table has three rows, 1st row is table header, 2nd
row is Master data and 3rd row is place where I put mine child
table(subreport).
If my subreport doesnt have any data this row is still visible(empty
space), I search all over internet and didnt find resolve for this
problem. Im working with Visual Studio 20005.
Is there any way for finding out number of rows in subreport, and use
that in Visibility expression of the 3rd row'
Thanks in advance,
Alen Husanovic
"Bruce L-C [MVP]" wrote:
> And the problem is?
> When asking for help you need to include the question. If you make it
> difficult to find out the question then you decrease the chance of someone
> answering it. In this case I don't have the starting post on this thread so
> I don't have a clue what your problem is.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MikeS" <MikeS@.discussions.microsoft.com> wrote in message
> news:5015E622-82AA-4446-846A-A5A039891708@.microsoft.com...
> >I am, also, having the same problem. Does anyone have a solution or isn't
> > there one? I would appreciate some kind of a reply.
> >
> > "bjkaledas" wrote:
> >
> >> I am having the exact same problem. Anybody have a proposed solution?
> >>
> >> "alenhusanovic@.gmail.com" wrote:
> >>
> >> > anyone?
> >> >
> >> >
>
>|||There is a norows property where you can have text be displayed when there
is no data but I am not aware of a way to do what you desire here.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ek%23Wv%23MCHHA.3924@.TK2MSFTNGP02.phx.gbl...
> And the problem is?
> When asking for help you need to include the question. If you make it
> difficult to find out the question then you decrease the chance of someone
> answering it. In this case I don't have the starting post on this thread
> so I don't have a clue what your problem is.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MikeS" <MikeS@.discussions.microsoft.com> wrote in message
> news:5015E622-82AA-4446-846A-A5A039891708@.microsoft.com...
>>I am, also, having the same problem. Does anyone have a solution or isn't
>> there one? I would appreciate some kind of a reply.
>> "bjkaledas" wrote:
>> I am having the exact same problem. Anybody have a proposed solution?
>> "alenhusanovic@.gmail.com" wrote:
>> > anyone?
>> >
>> >
>

Empty spaces in the detail row..

How can I get rid of empty spaces following the text value? This is
happening for a Comments field, the text is longer and RS seems to add some
extra blank lines to the row.
i have set both the properties of the cell to "Can Increase to accomodate
contents" and "Can decrease to accomodate contents."
and using Trim () to trauncate any spaces but still does not work.
Also, is there any way to merger 2 cells vertically?You need to make the initial size of the row / textbox as small as possible.
Vertical merging of cells is not supported in the current version.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"newmem" <""> wrote in message news:eCgyAPJWEHA.2844@.TK2MSFTNGP11.phx.gbl...
> How can I get rid of empty spaces following the text value? This is
> happening for a Comments field, the text is longer and RS seems to add
> some
> extra blank lines to the row.
> i have set both the properties of the cell to "Can Increase to accomodate
> contents" and "Can decrease to accomodate contents."
> and using Trim () to trauncate any spaces but still does not work.
> Also, is there any way to merger 2 cells vertically?
>

Empty Row In Table

I have a table, single table no grouping. The table can be empty, meaning
will not turn any results due to the condition in the query not being met.
In the report, if the table is empty, I want the row to display something
like "everything's OK".
I was trying to create an Expression for a cell in the table like this:
=IIF(IsNothing(Fields!XXXX.Value), "everything's OK", Fields!XXXX.Value)
This doesn't work. Any suggestions?
Thanks,
JamesLook at this
http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx
under the 10. Layout - Do you show errors in Red?
This talks about using the 'NoRows' property of the report table, also
about using a textbox and tweaking its visibility.
Sambhaji|||Maybe:
=IIF(Fields!XXXX.Value="", "everything's OK", Fields!XXXX.Value)
"James" <James@.discussions.microsoft.com> escribió en el mensaje
news:5B35F476-5894-400D-A3A3-56FAC0F0BC2E@.microsoft.com...
>I have a table, single table no grouping. The table can be empty, meaning
> will not turn any results due to the condition in the query not being met.
> In the report, if the table is empty, I want the row to display something
> like "everything's OK".
> I was trying to create an Expression for a cell in the table like this:
> =IIF(IsNothing(Fields!XXXX.Value), "everything's OK", Fields!XXXX.Value)
> This doesn't work. Any suggestions?
> Thanks,
> James