Empty Column: sqlserver

Showing posts with label sqlserver. Show all posts

Monday, March 26, 2012

encryption of column in sqlserver 2000

Hi
I have some sensitive info like Credit card number in some of my
database tables. Is there a way to encrypt these credit card information in
database. I am running SQL server 2000 standard edition
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> I have some sensitive info like Credit card number in some of
> my database tables. Is there a way to encrypt these credit card
> information in database. I am running SQL server 2000 standard edition
> Thanks
> Mangesh
No native support for encryption in the database. You can
encrypt/decrypt in the application if you need to. There are a number of
freely available encryption algorithms you can use. Or you could try
securing the database to keep prying eyes away. What are you mostly
concerned about?
--
David Gugick
Imceda Software
www.imceda.com|||Thanks
I am concerned about the credit card information in the database.
What is the best and most widely encryption tools used with SQL server 2000.
Mangesh
"David Gugick" wrote:
> Mangesh Deshpande wrote:
> > Hi
> >
> > I have some sensitive info like Credit card number in some of
> > my database tables. Is there a way to encrypt these credit card
> > information in database. I am running SQL server 2000 standard edition
> >
> > Thanks
> > Mangesh
> No native support for encryption in the database. You can
> encrypt/decrypt in the application if you need to. There are a number of
> freely available encryption algorithms you can use. Or you could try
> securing the database to keep prying eyes away. What are you mostly
> concerned about?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Mangesh Deshpande wrote:
> Thanks
> I am concerned about the credit card information in the database.
> What is the best and most widely encryption tools used with SQL
> server 2000.
>
Yes, but why are you concerned. Who is going to be able to access this
data? How are they going to access it? Do you have security in place to
prevent unauthorized access to the underlying tables? How does one
currently access the CC information?
You could remove all access to the table and provide a view with a
computed column that just returns the last 4 digits of the CC number for
display: For example:
Create Table order_info3453453 (
cust_name varchar(25) not null,
cc_nbr varchar(16) not null)
DENY ALL ON order_info3453453 TO public
insert into order_info3453453 values ('David', '234234234234234')
insert into order_info3453453 values ('Jeff', '3784662345444441')
Create View order_info_view4324234
as
Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
From order_info3453453
grant select on order_info_view4324234 to public
select * from order_info_view4324234
cust_name cc_nbr
-- --
David 4234
David 4234
Jeff 4441
I saw your other post regarding backups, so maybe that's what you're
after.
--
David Gugick
Imceda Software
www.imceda.com|||Thanks David
I am primarily a oracle DBA and we used oracle supplied encryption which
allows the CC info to be accessed from certain IPs. The users are all
protected but management is worried in case the userinfo is disclosed and
unauthorized user try to access the DB he should be blocked from seeing CC
and password info in database by IP and first and second by encryption.
Management kind of like that idea, but now they want me to do the same thing
in sqlserver and was looking for various possible scenarious.
I found xp_encrypt and tested it but I am not sure if there any good tools
avaiable.
"David Gugick" wrote:
> Mangesh Deshpande wrote:
> > Thanks
> > I am concerned about the credit card information in the database.
> > What is the best and most widely encryption tools used with SQL
> > server 2000.
> >
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Mangesh Deshpande wrote:
> Thanks David
> I am primarily a oracle DBA and we used oracle supplied encryption
> which allows the CC info to be accessed from certain IPs. The users
> are all protected but management is worried in case the userinfo is
> disclosed and unauthorized user try to access the DB he should be
> blocked from seeing CC and password info in database by IP and first
> and second by encryption.
> Management kind of like that idea, but now they want me to do the
> same thing in sqlserver and was looking for various possible
> scenarious.
> I found xp_encrypt and tested it but I am not sure if there any good
> tools avaiable.
Maybe this will help:
http://www.windowsitpro.com/Article/ArticleID/44550/44550.html?Ad=1
Go a google search for ["SQL Server" Encryption] and see what comes up.
There are third-party solutions available for data encryption. You could
also manage this from the app or middle-tier if necessary.
Regarding your comments about unauthorized access:
You can revoke access to the underlying tables using built-in security.
You can grant access to the data to the necessary groups/individuals
using specific grants on stored procedures. If no one has SELECT rights
on the table and cannot execute a procedure that selects the data, then
no one can see it. If you use the computed column technique as I
mentioned in my last post, you can show end users the last 4 digits of
the cc number so those in the company that require access to information
for contact with customers can have enough to go on (obviously they
could also have access to the type of card).
So how would an unauthorized user access the database? Well, first they
would need a valid login and would also need to have sufficient rights
to execute the necessary procedures to return the customer information.
You can probably limit access to the server by IP / MAC Address using
your firewall.
For backups, you do need encryption. And as I mentioned I would check
out LiteSpeed (free trial on web site).
David Gugick
Imceda Software
www.imceda.com|||The encryption/decryption of the data is better handled in the presentation
layer tier of the system. There, you can use the native Crypto32 API calls.
If you are using any of the .NET compilers, these are exposed through
System.Security calls.
Sincerely,
Anthony Thomas
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
Mangesh Deshpande wrote:
> Thanks
> I am concerned about the credit card information in the database.
> What is the best and most widely encryption tools used with SQL
> server 2000.
>
Yes, but why are you concerned. Who is going to be able to access this
data? How are they going to access it? Do you have security in place to
prevent unauthorized access to the underlying tables? How does one
currently access the CC information?
You could remove all access to the table and provide a view with a
computed column that just returns the last 4 digits of the CC number for
display: For example:
Create Table order_info3453453 (
cust_name varchar(25) not null,
cc_nbr varchar(16) not null)
DENY ALL ON order_info3453453 TO public
insert into order_info3453453 values ('David', '234234234234234')
insert into order_info3453453 values ('Jeff', '3784662345444441')
Create View order_info_view4324234
as
Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
From order_info3453453
grant select on order_info_view4324234 to public
select * from order_info_view4324234
cust_name cc_nbr
-- --
David 4234
David 4234
Jeff 4441
I saw your other post regarding backups, so maybe that's what you're
after.
--
David Gugick
Imceda Software
www.imceda.com|||I personally like this idea, but cannot imagine how to do that. Any clues ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> The encryption/decryption of the data is better handled in the
presentation
> layer tier of the system. There, you can use the native Crypto32 API
calls.
> If you are using any of the .NET compilers, these are exposed through
> System.Security calls.
> Sincerely,
>
> Anthony Thomas
>
> --
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> > Thanks
> > I am concerned about the credit card information in the database.
> > What is the best and most widely encryption tools used with SQL
> > server 2000.
> >
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||I just told you: the .NET framework exposes the Crypto API through the
System.Security objects.
Sincerely,
Anthony Thomas
"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:OaaZaK0LFHA.1144@.TK2MSFTNGP09.phx.gbl...
I personally like this idea, but cannot imagine how to do that. Any clues ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> The encryption/decryption of the data is better handled in the
presentation
> layer tier of the system. There, you can use the native Crypto32 API
calls.
> If you are using any of the .NET compilers, these are exposed through
> System.Security calls.
> Sincerely,
>
> Anthony Thomas
>
> --
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> > Thanks
> > I am concerned about the credit card information in the database.
> > What is the best and most widely encryption tools used with SQL
> > server 2000.
> >
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Hi Antony,
O.K. I will try to restate the question.
For example I am using datagrid bound to table in some dataset.
Data table is populated when form is loaded, and data source is updated when
I close the form.
So I can use some logic to decrypt data from table when the form is going to
load and I can encrypt data again before the form is closed. Do you suggest
writing special dataadapter class, or might be some different approach ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uAyhm73LFHA.2420@.TK2MSFTNGP12.phx.gbl...
> I just told you: the .NET framework exposes the Crypto API through the
> System.Security objects.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:OaaZaK0LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> I personally like this idea, but cannot imagine how to do that. Any clues
?
> Regards,
> Daniel
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> > The encryption/decryption of the data is better handled in the
> presentation
> > layer tier of the system. There, you can use the native Crypto32 API
> calls.
> > If you are using any of the .NET compilers, these are exposed through
> > System.Security calls.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> > news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> > Mangesh Deshpande wrote:
> > > Thanks
> > > I am concerned about the credit card information in the database.
> > > What is the best and most widely encryption tools used with SQL
> > > server 2000.
> > >
> >
> > Yes, but why are you concerned. Who is going to be able to access this
> > data? How are they going to access it? Do you have security in place to
> > prevent unauthorized access to the underlying tables? How does one
> > currently access the CC information?
> >
> > You could remove all access to the table and provide a view with a
> > computed column that just returns the last 4 digits of the CC number for
> > display: For example:
> >
> > Create Table order_info3453453 (
> > cust_name varchar(25) not null,
> > cc_nbr varchar(16) not null)
> >
> > DENY ALL ON order_info3453453 TO public
> >
> > insert into order_info3453453 values ('David', '234234234234234')
> > insert into order_info3453453 values ('Jeff', '3784662345444441')
> >
> > Create View order_info_view4324234
> > as
> > Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> > From order_info3453453
> >
> > grant select on order_info_view4324234 to public
> >
> > select * from order_info_view4324234
> >
> > cust_name cc_nbr
> > -- --
> > David 4234
> > David 4234
> > Jeff 4441
> >
> >
> > I saw your other post regarding backups, so maybe that's what you're
> > after.
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >
> >
>|||If you do it that way, your system will be SLOOOOW.
I would bind your grid to the desciptive part of the result set (unencrypted
parts). Then, when you users find a particular result they want additional
information on or want to modify, I would launch an independent dialogue
that decrypts the entire contents, but for that one row only.
Allowing users to edit a bound grid is a bad design choice for concurrent
systems anyway, and this way you will only have to run through the
decrypt/encrypt algorythms only when a use specifically request that single
piece of information. You do not want to have to incur this additional
logic while the users are merely searching for the information.
Sincerely,
Anthony Thomas
"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:e9hiS$AMFHA.3184@.TK2MSFTNGP09.phx.gbl...
Hi Antony,
O.K. I will try to restate the question.
For example I am using datagrid bound to table in some dataset.
Data table is populated when form is loaded, and data source is updated when
I close the form.
So I can use some logic to decrypt data from table when the form is going to
load and I can encrypt data again before the form is closed. Do you suggest
writing special dataadapter class, or might be some different approach ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uAyhm73LFHA.2420@.TK2MSFTNGP12.phx.gbl...
> I just told you: the .NET framework exposes the Crypto API through the
> System.Security objects.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:OaaZaK0LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> I personally like this idea, but cannot imagine how to do that. Any clues
?
> Regards,
> Daniel
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> > The encryption/decryption of the data is better handled in the
> presentation
> > layer tier of the system. There, you can use the native Crypto32 API
> calls.
> > If you are using any of the .NET compilers, these are exposed through
> > System.Security calls.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> > news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> > Mangesh Deshpande wrote:
> > > Thanks
> > > I am concerned about the credit card information in the database.
> > > What is the best and most widely encryption tools used with SQL
> > > server 2000.
> > >
> >
> > Yes, but why are you concerned. Who is going to be able to access this
> > data? How are they going to access it? Do you have security in place to
> > prevent unauthorized access to the underlying tables? How does one
> > currently access the CC information?
> >
> > You could remove all access to the table and provide a view with a
> > computed column that just returns the last 4 digits of the CC number for
> > display: For example:
> >
> > Create Table order_info3453453 (
> > cust_name varchar(25) not null,
> > cc_nbr varchar(16) not null)
> >
> > DENY ALL ON order_info3453453 TO public
> >
> > insert into order_info3453453 values ('David', '234234234234234')
> > insert into order_info3453453 values ('Jeff', '3784662345444441')
> >
> > Create View order_info_view4324234
> > as
> > Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> > From order_info3453453
> >
> > grant select on order_info_view4324234 to public
> >
> > select * from order_info_view4324234
> >
> > cust_name cc_nbr
> > -- --
> > David 4234
> > David 4234
> > Jeff 4441
> >
> >
> > I saw your other post regarding backups, so maybe that's what you're
> > after.
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >
> >
>

encryption of column in sqlserver 2000

Encryption in SQL Server 2005.

Can anyone tell me what is the best approach for Encyrption in SQLServer 200
5
? If there is any sample scripts that you link me to. Thanks,
Sam.
Message posted via http://www.droptable.comHow much of your data are you encrypting?
"SAM via droptable.com" <u33747@.uwe> wrote in message
news:7c011375421da@.uwe...
> Can anyone tell me what is the best approach for Encyrption in SQLServer
> 2005
> ? If there is any sample scripts that you link me to. Thanks,
> Sam.
> --
> Message posted via http://www.droptable.com
>|||I need to encrypt 2 colums in a table.Which contains the SQL login and the
password. The record count is around 100.
Decryption should be done by specific sql user /or role /or a Window Active
directory login. Any help would be appreciated. Thanks
Mike C# wrote:[vbcol=seagreen]
>How much of your data are you encrypting?
>
Message posted via http://www.droptable.com|||Check this out
Simple demo for how to encrypt and decrypt a table column in SQL Server 2005
http://blogs.msdn.com/lcris/archive.../09/427523.aspx
This uses the concept of keys in SQL Server 2005 with samples
"Prem via droptable.com" <u33747@.uwe> wrote in message
news:7c01762276e56@.uwe...
>I need to encrypt 2 colums in a table.Which contains the SQL login and the
> password. The record count is around 100.
> Decryption should be done by specific sql user /or role /or a Window
> Active
> directory login. Any help would be appreciated. Thanks
>
> Mike C# wrote:
> --
> Message posted via http://www.droptable.com
>

Monday, March 19, 2012

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

Encrypted value shown as '?' in a column of type varchar

Dear All,

I inserted a record in table on DB created on SQLServer 2005 and found out that the one of the column values is shown as '?' instead of showing the encrypted value that I sent with the insert statement.3

............................ Can anyone tell me how to get rid of this?

Thanks and regards,

Z Z.

How are you encrypting the data and how are you retrieving it?|||Thanks for your reply. Actually I'm using only one-way encryption and seen these '?' through SQL Server Management Studio by directly viewing the table contents.|||So what are you expecting to see returned if you are using one way encryption?|||

I was expecting to see encrypted value when I opened the database table directly from within the SQL Server Management Studio. Instead I found '?' only. Anyway, I am done with it and used two encryption mechanism. Thanks a lot.

Encrypted Stored Proc Performance

Does anyone know if there executing encrypted stored procedures in SQL
Server 2005 is any slower than regular ones?
Thanks,
KripHi, Krip,
There really should be no difference in performance, since the execution of
the stored procedure is not from the text. Maybe recompiles take slightly
longer, but I would not know.
The gentleman in this thread from 2004 felt that his encrypted stored
procedures ran faster, but the full testing method was questioned.
http://groups.google.com/group/micr...80e7a94e230ea8f
From the same time period Jacco Schalkwijk (SQL Server MVP) noted that the
difference was negligable.
RLF
"Krip" <amk@.kynetix.com> wrote in message
news:DC3CA6E3-A4D6-4B1C-99AB-88660359C9B7@.microsoft.com...
> Does anyone know if there executing encrypted stored procedures in SQL
> Server 2005 is any slower than regular ones?
> Thanks,
> Krip
>|||Thanks Russell. Makes sense. Good to hear from you!
-Krip

Sunday, March 11, 2012

EncryptByCert Problem

I posted this message to the wrong group (sqlserver.programming), so I'm
reposting here. Sorry for the repost... it's late... so here goes:
OK, so I'm testing EncryptByCert with some code like the following:
DECLARE @.v varchar(8000);
SELECT @.v = REPLICATE('A', 117);
SELECT @.v;
DECLARE @.e varbinary(8000);
SELECT @.e = EncryptByCert(Cert_ID(N'TestCertificate'
), @.v);
SELECT @.e;
The function encrypts fine and I'm able to use DecryptByCert to get the
result. Problem is if I change the line SELECT @.v = REPLICATE('A', 117) to:
SELECT @.v = REPLICATE('A', 118);
The EncryptByCert function returns NULL every time with 118 or higher. BOL
states that the result is returned as a varbinary with a max length of
8,000. I keep getting a varbinary with a max length of 128. Can anyone
else reproduce this, or am I doing something wrong?
ThanksYes, this is a restriction of asymmetric key encryption - the actual data
that you can encrypt with one call will depend on the size of the private
key that is used.
For example: 512 bit RSA key can encrypt up to 53 bytes, 1024 bit up to 117
bytes, and 2048 bit up to 245 bytes.
See http://blogs.msdn.com/yukondoit/arc.../24/496521.aspx for a
workaround, if you really want to encrypt more.
Certificates should only be used to protect other keys, not to encrypt data
directly, so this limit does not impact the intended use of certificates.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike C#" <xyz@.xyz.com> wrote in message
news:fuaHg.586$k%3.406@.newsfe12.lga...
>I posted this message to the wrong group (sqlserver.programming), so I'm
>reposting here. Sorry for the repost... it's late... so here goes:
> OK, so I'm testing EncryptByCert with some code like the following:
> DECLARE @.v varchar(8000);
> SELECT @.v = REPLICATE('A', 117);
> SELECT @.v;
> DECLARE @.e varbinary(8000);
> SELECT @.e = EncryptByCert(Cert_ID(N'TestCertificate'
), @.v);
> SELECT @.e;
> The function encrypts fine and I'm able to use DecryptByCert to get the
> result. Problem is if I change the line SELECT @.v = REPLICATE('A', 117)
> to:
> SELECT @.v = REPLICATE('A', 118);
> The EncryptByCert function returns NULL every time with 118 or higher.
> BOL states that the result is returned as a varbinary with a max length of
> 8,000. I keep getting a varbinary with a max length of 128. Can anyone
> else reproduce this, or am I doing something wrong?
> Thanks
>|||Someone needs to tell the BOL writers to update the page on this then. It
currently indicates that EncryptByCert can return a varbinary up to 8,000
bytes in length, which is ludicrous if the input is really limited to 421
bytes and the output is limited to 432 bytes. I'm not advocating using
certificates and asymmetric keys to encrypt all data. I am advocating
accurate documentation of system limitations.
"Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in message
news:e2ASWY6xGHA.4336@.TK2MSFTNGP06.phx.gbl...
> Yes, this is a restriction of asymmetric key encryption - the actual data
> that you can encrypt with one call will depend on the size of the private
> key that is used.
> For example: 512 bit RSA key can encrypt up to 53 bytes, 1024 bit up to
> 117 bytes, and 2048 bit up to 245 bytes.
> See http://blogs.msdn.com/yukondoit/arc.../24/496521.aspx for a
> workaround, if you really want to encrypt more.
> Certificates should only be used to protect other keys, not to encrypt
> data directly, so this limit does not impact the intended use of
> certificates.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Design Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:fuaHg.586$k%3.406@.newsfe12.lga...
>|||I'll ask for an update of these BOL topics to mention the restrictions
related to the key lenghts. If you access BOL online, you can also rate the
articles and submit feedback directly. Here are the links for the topics we
discussed:
http://msdn2.microsoft.com/en-us/library/ms188061.aspx
http://msdn2.microsoft.com/en-us/library/ms186950.aspx
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23vuAcc6xGHA.3500@.TK2MSFTNGP02.phx.gbl...
> Someone needs to tell the BOL writers to update the page on this then. It
> currently indicates that EncryptByCert can return a varbinary up to 8,000
> bytes in length, which is ludicrous if the input is really limited to 421
> bytes and the output is limited to 432 bytes. I'm not advocating using
> certificates and asymmetric keys to encrypt all data. I am advocating
> accurate documentation of system limitations.
> "Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in message
> news:e2ASWY6xGHA.4336@.TK2MSFTNGP06.phx.gbl...
>|||Thanks. I wanted to get verification and make sure it wasn't a problem with
my installation or how I was using the functions before I claimed errors in
either the application or the documentation.
"Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in message
news:Ovyex47xGHA.1288@.TK2MSFTNGP03.phx.gbl...
> I'll ask for an update of these BOL topics to mention the restrictions
> related to the key lenghts. If you access BOL online, you can also rate
> the articles and submit feedback directly. Here are the links for the
> topics we discussed:
> http://msdn2.microsoft.com/en-us/library/ms188061.aspx
> http://msdn2.microsoft.com/en-us/library/ms186950.aspx
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Design Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:%23vuAcc6xGHA.3500@.TK2MSFTNGP02.phx.gbl...
>|||Also, in the meantime, you may want to use the following reference:
http://msdn.microsoft.com/library/d...ryptencrypt.asp
The relevant paragraph extracted from above is:
The Microsoft Enhanced Cryptographic Provider supports direct encryption
with RSA public keys and decryption with RSA private keys. The encryption
uses PKCS #1 padding. On decryption, this padding is verified. The length of
plaintext data that can be encrypted with a call to CryptEncrypt with an RSA
key is the length of the key modulus minus eleven bytes. The eleven bytes is
the chosen minimum for PKCS #1 padding. The ciphertext is returned in
little-endian format.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in message
news:Ovyex47xGHA.1288@.TK2MSFTNGP03.phx.gbl...
> I'll ask for an update of these BOL topics to mention the restrictions
> related to the key lenghts. If you access BOL online, you can also rate
> the articles and submit feedback directly. Here are the links for the
> topics we discussed:
> http://msdn2.microsoft.com/en-us/library/ms188061.aspx
> http://msdn2.microsoft.com/en-us/library/ms186950.aspx
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Design Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:%23vuAcc6xGHA.3500@.TK2MSFTNGP02.phx.gbl...
>

Wednesday, March 7, 2012

Enabling SQL Server Agent by using scripts

Hi NG,
how do I have to enable the SQL Server Agent by using command line scripts
(without the Enterprise Manager). According to SQLServer books online the
command line command sqlagent should only be used for diagnostic purposes.
thanks for help, reneRene,
To start SQL Server Agent from the commandline you can do this:
net start sqlserveragent
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Re Fo wrote:
> Hi NG,
> how do I have to enable the SQL Server Agent by using command line scripts
> (without the Enterprise Manager). According to SQLServer books online the
> command line command sqlagent should only be used for diagnostic purposes.
> thanks for help, rene
>|||Thanks,
can I set it somewhere in the registry that the service will alwyas be
started, as I can set it in the Services properties?
Thanks for help, rene
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:ec0Gbwf4EHA.1976@.TK2MSFTNGP09.phx.gbl...
> Rene,
> To start SQL Server Agent from the commandline you can do this:
> net start sqlserveragent
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Re Fo wrote:
> > Hi NG,
> >
> > how do I have to enable the SQL Server Agent by using command line
scripts
> > (without the Enterprise Manager). According to SQLServer books online
the
> > command line command sqlagent should only be used for diagnostic
purposes.
> >
> > thanks for help, rene
> >
> >|||There are two places you can do this.
1)
Enterprise Manager-->General Tab, Tick AutoStart SQL Server Agent
2)
In Services Control Panel Applet, you can set the SQLServerAgent service
to AutoStart.
or if these don't work, you could even do this:
3)
CREATE PROC sp_startagent
AS
exec master..xp_cmdshell 'net start sqlserveragent', no_output
GO
exec sp_procoption N'sp_startagent', N'startup', N'true'
GO
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Re Fo wrote:
> Thanks,
> can I set it somewhere in the registry that the service will alwyas be
> started, as I can set it in the Services properties?
> Thanks for help, rene
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:ec0Gbwf4EHA.1976@.TK2MSFTNGP09.phx.gbl...
>>Rene,
>>To start SQL Server Agent from the commandline you can do this:
>>net start sqlserveragent
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>Re Fo wrote:
>>Hi NG,
>>how do I have to enable the SQL Server Agent by using command line
> scripts
>>(without the Enterprise Manager). According to SQLServer books online
> the
>>command line command sqlagent should only be used for diagnostic
> purposes.
>>thanks for help, rene
>>
>
>

Enabling SQL Server Agent by using scripts

Hi NG,
how do I have to enable the SQL Server Agent by using command line scripts
(without the Enterprise Manager). According to SQLServer books online the
command line command sqlagent should only be used for diagnostic purposes.
thanks for help, rene
Rene,
To start SQL Server Agent from the commandline you can do this:
net start sqlserveragent
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Re Fo wrote:
> Hi NG,
> how do I have to enable the SQL Server Agent by using command line scripts
> (without the Enterprise Manager). According to SQLServer books online the
> command line command sqlagent should only be used for diagnostic purposes.
> thanks for help, rene
>
|||Thanks,
can I set it somewhere in the registry that the service will alwyas be
started, as I can set it in the Services properties?
Thanks for help, rene
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:ec0Gbwf4EHA.1976@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Rene,
> To start SQL Server Agent from the commandline you can do this:
> net start sqlserveragent
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Re Fo wrote:
scripts[vbcol=seagreen]
the[vbcol=seagreen]
purposes.[vbcol=seagreen]
|||There are two places you can do this.
1)
Enterprise Manager-->General Tab, Tick AutoStart SQL Server Agent
2)
In Services Control Panel Applet, you can set the SQLServerAgent service
to AutoStart.
or if these don't work, you could even do this:
3)
CREATE PROC sp_startagent
AS
exec master..xp_cmdshell 'net start sqlserveragent', no_output
GO
exec sp_procoption N'sp_startagent', N'startup', N'true'
GO
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Re Fo wrote:
> Thanks,
> can I set it somewhere in the registry that the service will alwyas be
> started, as I can set it in the Services properties?
> Thanks for help, rene
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:ec0Gbwf4EHA.1976@.TK2MSFTNGP09.phx.gbl...
>
> scripts
>
> the
>
> purposes.
>
>

Sunday, February 26, 2012

enabling AWE

After enabling awe and setting the max server memory, the
Task Manager, Process for sqlserver.exe shows 90MB - is
this normal? I have configured sql server to use 6GB!!!!
How can I be sure the memory is set up correctly?
CBUse perfmon and view the sql server memory counters instead of task manager.
Specifically the target and total memory should show approx 6GB. Did you
set the /PAE switch in the boot.ini as well? How about the /3GB?
87 Shrinking TempDB
http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
http://www.support.microsoft.com/?id=274750 Memory config
http://www.support.microsoft.com/?id=283037 Large Memory Support Is
Available in Windows 2000 (AWE)
http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
memory
--
Andrew J. Kelly SQL MVP
"CB" <anonymous@.discussions.microsoft.com> wrote in message
news:64e201c4cb40$8dab3650$a601280a@.phx.gbl...
> After enabling awe and setting the max server memory, the
> Task Manager, Process for sqlserver.exe shows 90MB - is
> this normal? I have configured sql server to use 6GB!!!!
> How can I be sure the memory is set up correctly?
> CB

Enabling AWE

Hi,
Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
Server 2005 EE to be able to use whole of memory if required.
I have already used "awe enabled" option with sp_configure to change the run
value to 1, but it seems other parameters must be configured on OS.
What's the easiest way to do it?
Many thanks in advance,
LeilaAt the OS level, you have to set the /PAE switch in BOOT.INI. That said,
your machine doesn't really have enough RAM. You don't want to give 100% of
the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
some before you turn on AWE.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
Hi,
Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
Server 2005 EE to be able to use whole of memory if required.
I have already used "awe enabled" option with sp_configure to change the run
value to 1, but it seems other parameters must be configured on OS.
What's the easiest way to do it?
Many thanks in advance,
Leila|||That is NOT correct. AWE can be enabled without PAE. Using PAE allows the OS
to address more than 4 GB of memory. AWE is an API set that applications
(like SQL) can leverage to reserve chunks of memory for manipulation.
Since he only has 3.5 GB of memory, PAE would get him nothing. Also, SQL
will never be able to reserve all of the memory. The OS needs some, other
services, etc., will also use some memory.
"Tom Moreau" wrote:

> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100%
of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the r
un
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>|||Does your boot.ini have the /3GB switch? If not, the operating system will
not be able to allocate all of your memory.
Also, I highly recommend NOT allowing SQL Server to consume *all* of the
memory. The OS needs some, too.
A
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>|||You would be best served, UNTIL you get more memory, to use the /3GB switch.
The /3GB switch gives SQL Server up to 3 GB of memory. The OS requires
memory, so leaving the remaining 512 MB for the OS seems about right.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>|||You would be best served, UNTIL you get more memory, to use the /3GB switch.
The /3GB switch gives SQL Server up to 3 GB of memory. The OS requires
memory, so leaving the remaining 512 MB for the OS seems about right.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>|||Thanks every body!
What's the exact parameter that I must add to boot.ini?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OAryIzzBHHA.4892@.TK2MSFTNGP04.phx.gbl...
> Does your boot.ini have the /3GB switch? If not, the operating system
> will not be able to allocate all of your memory.
> Also, I highly recommend NOT allowing SQL Server to consume *all* of the
> memory. The OS needs some, too.
> A
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
>|||/3GB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:eH4iWF1BHHA.4992@.TK2MSFTNGP03.phx.gbl...
> Thanks every body!
> What's the exact parameter that I must add to boot.ini?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OAryIzzBHHA.4892@.TK2MSFTNGP04.phx.gbl...
>|||Hi All,
I need some help here:
We have Windows 2000 Advanced Server sp4
SQL Server 2000 EE sp4 plus HOT Fix (KB899761)
Boot.ini filoe has /3GB switch
Total Memory Available = 8.0 GB
SQL Server configuration as follow:
min memory per query (KB) = 2048
max server memory (MB) = 5097
The SQL Server service account seems to have all appropriate permisison
(lock memory etc.)
But it seems that SQL Server can only allocate approx 2.4 GB of memory
Any help would be greatly appreciated!
Kamal
"Tom Moreau" wrote:

> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100%
of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the r
un
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>|||Run sp_configure to enable AWE, and set the max server memory. be sure to
leave some memory for the OS. Also, set the /PAE switch in BOOT.INI and
reboot.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Kamal Hassan" <KamalHassan@.discussions.microsoft.com> wrote in message
news:92951B59-BB92-47B3-A72F-C544D8DF01C1@.microsoft.com...
Hi All,
I need some help here:
We have Windows 2000 Advanced Server sp4
SQL Server 2000 EE sp4 plus HOT Fix (KB899761)
Boot.ini filoe has /3GB switch
Total Memory Available = 8.0 GB
SQL Server configuration as follow:
min memory per query (KB) = 2048
max server memory (MB) = 5097
The SQL Server service account seems to have all appropriate permisison
(lock memory etc.)
But it seems that SQL Server can only allocate approx 2.4 GB of memory
Any help would be greatly appreciated!
Kamal
"Tom Moreau" wrote:

> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100%
> of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>

enabling AWE

After enabling awe and setting the max server memory, the
Task Manager, Process for sqlserver.exe shows 90MB - is
this normal? I have configured sql server to use 6GB!!!!
How can I be sure the memory is set up correctly?
CBUse perfmon and view the sql server memory counters instead of task manager.
Specifically the target and total memory should show approx 6GB. Did you
set the /PAE switch in the boot.ini as well? How about the /3GB?
87 Shrinking TempDB
http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
http://www.support.microsoft.com/?id=274750 Memory config
http://www.support.microsoft.com/?id=283037 Large Memory Support Is
Available in Windows 2000 (AWE)
http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
memory
Andrew J. Kelly SQL MVP
"CB" <anonymous@.discussions.microsoft.com> wrote in message
news:64e201c4cb40$8dab3650$a601280a@.phx.gbl...
> After enabling awe and setting the max server memory, the
> Task Manager, Process for sqlserver.exe shows 90MB - is
> this normal? I have configured sql server to use 6GB!!!!
> How can I be sure the memory is set up correctly?
> CB

Enabling AWE

Hi,
Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
Server 2005 EE to be able to use whole of memory if required.
I have already used "awe enabled" option with sp_configure to change the run
value to 1, but it seems other parameters must be configured on OS.
What's the easiest way to do it?
Many thanks in advance,
Leila
At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
your machine doesn't really have enough RAM. You don't want to give 100% of
the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
some before you turn on AWE.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
Hi,
Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
Server 2005 EE to be able to use whole of memory if required.
I have already used "awe enabled" option with sp_configure to change the run
value to 1, but it seems other parameters must be configured on OS.
What's the easiest way to do it?
Many thanks in advance,
Leila
|||That is NOT correct. AWE can be enabled without PAE. Using PAE allows the OS
to address more than 4 GB of memory. AWE is an API set that applications
(like SQL) can leverage to reserve chunks of memory for manipulation.
Since he only has 3.5 GB of memory, PAE would get him nothing. Also, SQL
will never be able to reserve all of the memory. The OS needs some, other
services, etc., will also use some memory.
"Tom Moreau" wrote:

> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100% of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the run
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>
|||Does your boot.ini have the /3GB switch? If not, the operating system will
not be able to allocate all of your memory.
Also, I highly recommend NOT allowing SQL Server to consume *all* of the
memory. The OS needs some, too.
A
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
|||You would be best served, UNTIL you get more memory, to use the /3GB switch.
The /3GB switch gives SQL Server up to 3 GB of memory. The OS requires
memory, so leaving the remaining 512 MB for the OS seems about right.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
|||You would be best served, UNTIL you get more memory, to use the /3GB switch.
The /3GB switch gives SQL Server up to 3 GB of memory. The OS requires
memory, so leaving the remaining 512 MB for the OS seems about right.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
|||Thanks every body!
What's the exact parameter that I must add to boot.ini?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OAryIzzBHHA.4892@.TK2MSFTNGP04.phx.gbl...
> Does your boot.ini have the /3GB switch? If not, the operating system
> will not be able to allocate all of your memory.
> Also, I highly recommend NOT allowing SQL Server to consume *all* of the
> memory. The OS needs some, too.
> A
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
>
|||/3GB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:eH4iWF1BHHA.4992@.TK2MSFTNGP03.phx.gbl...
> Thanks every body!
> What's the exact parameter that I must add to boot.ini?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OAryIzzBHHA.4892@.TK2MSFTNGP04.phx.gbl...
>
|||Hi All,
I need some help here:
We have Windows 2000 Advanced Server sp4
SQL Server 2000 EE sp4 plus HOT Fix (KB899761)
Boot.ini filoe has /3GB switch
Total Memory Available = 8.0 GB
SQL Server configuration as follow:
min memory per query (KB) = 2048
max server memory (MB) = 5097
The SQL Server service account seems to have all appropriate permisison
(lock memory etc.)
But it seems that SQL Server can only allocate approx 2.4 GB of memory
Any help would be greatly appreciated!
Kamal
"Tom Moreau" wrote:

> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100% of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the run
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>
|||Run sp_configure to enable AWE, and set the max server memory. be sure to
leave some memory for the OS. Also, set the /PAE switch in BOOT.INI and
reboot.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Kamal Hassan" <KamalHassan@.discussions.microsoft.com> wrote in message
news:92951B59-BB92-47B3-A72F-C544D8DF01C1@.microsoft.com...
Hi All,
I need some help here:
We have Windows 2000 Advanced Server sp4
SQL Server 2000 EE sp4 plus HOT Fix (KB899761)
Boot.ini filoe has /3GB switch
Total Memory Available = 8.0 GB
SQL Server configuration as follow:
min memory per query (KB) = 2048
max server memory (MB) = 5097
The SQL Server service account seems to have all appropriate permisison
(lock memory etc.)
But it seems that SQL Server can only allocate approx 2.4 GB of memory
Any help would be greatly appreciated!
Kamal
"Tom Moreau" wrote:

> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100%
> of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>

Monday, March 26, 2012

encryption of column in sqlserver 2000

encryption of column in sqlserver 2000

encryption of column in sqlserver 2000

Encryption in SQL Server 2005.

Monday, March 19, 2012

Encrypting a Single Row/Column

Encrypting a Single Row/Column

Encrypted value shown as '?' in a column of type varchar

Encrypted Stored Proc Performance

Sunday, March 11, 2012

EncryptByCert Problem

Wednesday, March 7, 2012

Enabling SQL Server Agent by using scripts

Enabling SQL Server Agent by using scripts

Sunday, February 26, 2012

enabling AWE

Enabling AWE

enabling AWE

Enabling AWE

Empty Column

Blog Archive

About Me