Empty Column: script

Showing posts with label script. Show all posts

Monday, March 19, 2012

Encryptin stored procedures

Is there any way to generate a script of an encrypted stored procedure?
I supose that itsn't, so, is there are any way to encrypt a script so that I
can generate a encrypted stored prodcedure?Search google for "decrypt SQL stored procedure"
Not sure what you mean by the second part. Specifying WITH ENCRYPTION for
your CREATE PROCEDURE script will generate an encrypted stored procedure but
the script itself is plain text.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gaby" <msdn@.rmya.com.ar> wrote in message
news:OcjhQ5CvEHA.944@.TK2MSFTNGP11.phx.gbl...
> Is there any way to generate a script of an encrypted stored procedure?
>
> I supose that itsn't, so, is there are any way to encrypt a script so that
> I
> can generate a encrypted stored prodcedure?
>|||Hi Gaby,
I am just checking on your progress regarding the information that was sent
you! Have you tried the google as MVP suggested you? I wonder how the
testing is going. If you encounter any difficulty, please do not hesitate
to let me know. Please post here and let me know the status of your issue.
Without your further information, it's very hard for me to continue with
the troubleshooting.
Looking forward to hearing from you soon
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Encrypted object is not transferable

Hi,
i m getting error "Encrypted object is not transferable, and script cannot be generated", while trying to open few stored procedure. It is extemly important for me to see the script of the sp as I need to study it.
help?
regards,
sim sim
Hi
The SP you are trying to look at is Encrypted.
There are a few tools on the NET to decrypt the SP. Look for "sql decrypt
sp" on Google.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sim sim" <simsim@.discussions.microsoft.com> wrote in message
news:930C6B12-8E72-4A27-BB16-12E744937B82@.microsoft.com...
> Hi,
> i m getting error "Encrypted object is not transferable, and script
cannot be generated", while trying to open few stored procedure. It is
extemly important for me to see the script of the sp as I need to study it.
> help?
> regards,
> sim sim
>
>

Encrypted object is not transferable

Hi,
i m getting error "Encrypted object is not transferable, and script cannot b
e generated", while trying to open few stored procedure. It is extemly impor
tant for me to see the script of the sp as I need to study it.
help'
regards,
sim simHi
The SP you are trying to look at is Encrypted.
There are a few tools on the NET to decrypt the SP. Look for "sql decrypt
sp" on Google.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sim sim" <simsim@.discussions.microsoft.com> wrote in message
news:930C6B12-8E72-4A27-BB16-12E744937B82@.microsoft.com...
> Hi,
> i m getting error "Encrypted object is not transferable, and script
cannot be generated", while trying to open few stored procedure. It is
extemly important for me to see the script of the sp as I need to study it.
> help'
> regards,
> sim sim
>
>

Sunday, March 11, 2012

Encrypt sproc still returns NULL's to non DBO's.

If someone would try out my script below I'd really appreciate it. Whenever
I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
in the execution. All ideas are welcomed.
TIA, ChrisR
USE [AdventureWorks];
GO
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'vato'
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
create procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OF
F
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
GRANT IMPERSONATE ON USER:: dbo TO test;
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/Hi Chris
I could not get you sample to produce the effect you say, but then I changed
the procedure to open/close the keys. You should have the keys open for as
short a time as possible
CREATE PROCEDURE getDecryptedIDNumber
WITH EXEC AS OWBER
AS
OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID Numb
er],
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS [Decrypted
ID
Number]
FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
GO
If this does not work you may want to post in
microsoft.public.sqlserver.security
A good source for encryption information is
http://blogs.msdn.com/lcris/archive/category/10357.aspx
http://blogs.msdn.com/lcris/archive.../13/512829.aspx will dop what you
but signs the procedure instead.
John
"ChrisR" wrote:

> If someone would try out my script below I'd really appreciate it. Wheneve
r
> I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite t
he
> fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo
'"
> in the execution. All ideas are welcomed.
> TIA, ChrisR
>
> USE [AdventureWorks];
> GO
> IF NOT EXISTS
> (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> CREATE MASTER KEY ENCRYPTION BY
> PASSWORD =
> 'vato'
> GO
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
> CREATE CERTIFICATE HumanResources037
> WITH SUBJECT = 'Employee Social Security Numbers';
> GO
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Create a column in which to store the encrypted data
> ALTER TABLE HumanResources.Employee
> ADD EncryptedNationalIDNumber varbinary(128);
> GO
> -- Open the symmetric key with which to encrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> -- Encrypt the value in column NationalIDNumber with symmetric
> -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> UPDATE HumanResources.Employee
> SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> NationalIDNumber);
> GO
> -- Verify the encryption.
> -- First, open the symmetric key with which to decrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Now list the original ID, the encrypted ID, and the
> -- decrypted ciphertext. If the decryption worked, the original
> -- and the decrypted ID will match.
> create procedure getDecryptedIDNumber
> with exec as owner
> as
> SELECT NationalIDNumber, EncryptedNationalIDNumber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> AS "Decrypted ID Number"
> FROM HumanResources.Employee;
> GO
> /*works for me, shows the decrypted data*/
> exec getDecryptedIDNumber
> USE [master]
> GO
> CREATE LOGIN [test] WITH PASSWORD=N'test',
> DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=
OFF
> GO
> USE [AdventureWorks]
> GO
> CREATE USER [test] FOR LOGIN [test]
> GO
> use [AdventureWorks]
> GO
> GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> GO
> GRANT IMPERSONATE ON USER:: dbo TO test;
> GO
> /*Now, open up a "file/new/DB Engine Query" and login with the test login*
/
> exec as user = 'dbo'
> exec getDecryptedIDNumber
> /*This returns NULL values where it should show the decrypted data*/
>
>|||Good enough, thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BD1F94F-E0F6-44EB-83FE-2C930BF1FF04@.microsoft.com...
> Hi Chris
> I could not get you sample to produce the effect you say, but then I
changed
> the procedure to open/close the keys. You should have the keys open for as
> short a time as possible
>
> CREATE PROCEDURE getDecryptedIDNumber
> WITH EXEC AS OWBER
> AS
> OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
> SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID
Number],
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS [Decrypted[/vbco
l]
ID[vbcol=seagreen]
> Number]
> FROM HumanResources.Employee;
> CLOSE SYMMETRIC KEY SSN_Key_01;
> GO
> If this does not work you may want to post in
> microsoft.public.sqlserver.security
> A good source for encryption information is
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> http://blogs.msdn.com/lcris/archive.../13/512829.aspx will dop what
you[vbcol=seagreen]
> but signs the procedure instead.
>
> John
> "ChrisR" wrote:
>
Whenever[vbcol=seagreen]
the[vbcol=seagreen]
'dbo'"[vbcol=seagreen]
CHECK_POLICY=OFF[vbcol=seagreen]
login*/[vbcol=seagreen]

Encrypt sproc still returns NULL's to non DBO's.

If someone would try out my script below I'd really appreciate it. Whenever
I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
in the execution. All ideas are welcomed.
TIA, ChrisR
USE [AdventureWorks];
GO
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'vato'
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
create procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
GRANT IMPERSONATE ON USER:: dbo TO test;
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/Good enough, thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BD1F94F-E0F6-44EB-83FE-2C930BF1FF04@.microsoft.com...
> Hi Chris
> I could not get you sample to produce the effect you say, but then I
changed
> the procedure to open/close the keys. You should have the keys open for as
> short a time as possible
>
> CREATE PROCEDURE getDecryptedIDNumber
> WITH EXEC AS OWBER
> AS
> OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
> SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID
Number],
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS [Decrypted
ID
> Number]
> FROM HumanResources.Employee;
> CLOSE SYMMETRIC KEY SSN_Key_01;
> GO
> If this does not work you may want to post in
> microsoft.public.sqlserver.security
> A good source for encryption information is
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx will dop what
you
> but signs the procedure instead.
>
> John
> "ChrisR" wrote:
> > If someone would try out my script below I'd really appreciate it.
Whenever
> > I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite
the
> > fact that I use "with exec as owner" in the sproc and "exec as user ='dbo'"
> > in the execution. All ideas are welcomed.
> >
> > TIA, ChrisR
> >
> >
> > USE [AdventureWorks];
> > GO
> >
> > IF NOT EXISTS
> > (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> > CREATE MASTER KEY ENCRYPTION BY
> > PASSWORD => > 'vato'
> > GO
> >
> > OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
> >
> > CREATE CERTIFICATE HumanResources037
> > WITH SUBJECT = 'Employee Social Security Numbers';
> > GO
> >
> > CREATE SYMMETRIC KEY SSN_Key_01
> > WITH ALGORITHM = DES
> > ENCRYPTION BY CERTIFICATE HumanResources037;
> > GO
> >
> > -- Create a column in which to store the encrypted data
> > ALTER TABLE HumanResources.Employee
> > ADD EncryptedNationalIDNumber varbinary(128);
> > GO
> >
> > -- Open the symmetric key with which to encrypt the data
> > OPEN SYMMETRIC KEY SSN_Key_01
> > DECRYPTION BY CERTIFICATE HumanResources037;
> >
> > -- Encrypt the value in column NationalIDNumber with symmetric
> > -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> > UPDATE HumanResources.Employee
> > SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> > NationalIDNumber);
> > GO
> >
> > -- Verify the encryption.
> > -- First, open the symmetric key with which to decrypt the data
> > OPEN SYMMETRIC KEY SSN_Key_01
> > DECRYPTION BY CERTIFICATE HumanResources037;
> > GO
> >
> > -- Now list the original ID, the encrypted ID, and the
> > -- decrypted ciphertext. If the decryption worked, the original
> > -- and the decrypted ID will match.
> >
> > create procedure getDecryptedIDNumber
> > with exec as owner
> > as
> > SELECT NationalIDNumber, EncryptedNationalIDNumber
> > AS "Encrypted ID Number",
> > CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> > AS "Decrypted ID Number"
> > FROM HumanResources.Employee;
> > GO
> >
> > /*works for me, shows the decrypted data*/
> >
> > exec getDecryptedIDNumber
> >
> > USE [master]
> > GO
> >
> > CREATE LOGIN [test] WITH PASSWORD=N'test',
> > DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
> > GO
> >
> > USE [AdventureWorks]
> > GO
> >
> > CREATE USER [test] FOR LOGIN [test]
> > GO
> >
> > use [AdventureWorks]
> > GO
> >
> > GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> > GO
> >
> > GRANT IMPERSONATE ON USER:: dbo TO test;
> > GO
> >
> > /*Now, open up a "file/new/DB Engine Query" and login with the test
login*/
> > exec as user = 'dbo'
> > exec getDecryptedIDNumber
> >
> > /*This returns NULL values where it should show the decrypted data*/
> >
> >
> >

Encrypt sproc still returns NULL's to non DBO's.

If someone would try out my script below I'd really appreciate it. Whenever
I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
in the execution. All ideas are welcomed.
TIA, ChrisR
USE [AdventureWorks];
GO
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'vato'
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
create procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
GRANT IMPERSONATE ON USER:: dbo TO test;
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/
Hi Chris
I could not get you sample to produce the effect you say, but then I changed
the procedure to open/close the keys. You should have the keys open for as
short a time as possible
CREATE PROCEDURE getDecryptedIDNumber
WITH EXEC AS OWBER
AS
OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID Number],
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS [Decrypted ID
Number]
FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
GO
If this does not work you may want to post in
microsoft.public.sqlserver.security
A good source for encryption information is
http://blogs.msdn.com/lcris/archive/category/10357.aspx
http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx will dop what you
but signs the procedure instead.
John
"ChrisR" wrote:

> If someone would try out my script below I'd really appreciate it. Whenever
> I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
> fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
> in the execution. All ideas are welcomed.
> TIA, ChrisR
>
> USE [AdventureWorks];
> GO
> IF NOT EXISTS
> (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> CREATE MASTER KEY ENCRYPTION BY
> PASSWORD =
> 'vato'
> GO
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
> CREATE CERTIFICATE HumanResources037
> WITH SUBJECT = 'Employee Social Security Numbers';
> GO
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Create a column in which to store the encrypted data
> ALTER TABLE HumanResources.Employee
> ADD EncryptedNationalIDNumber varbinary(128);
> GO
> -- Open the symmetric key with which to encrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> -- Encrypt the value in column NationalIDNumber with symmetric
> -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> UPDATE HumanResources.Employee
> SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> NationalIDNumber);
> GO
> -- Verify the encryption.
> -- First, open the symmetric key with which to decrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Now list the original ID, the encrypted ID, and the
> -- decrypted ciphertext. If the decryption worked, the original
> -- and the decrypted ID will match.
> create procedure getDecryptedIDNumber
> with exec as owner
> as
> SELECT NationalIDNumber, EncryptedNationalIDNumber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> AS "Decrypted ID Number"
> FROM HumanResources.Employee;
> GO
> /*works for me, shows the decrypted data*/
> exec getDecryptedIDNumber
> USE [master]
> GO
> CREATE LOGIN [test] WITH PASSWORD=N'test',
> DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
> GO
> USE [AdventureWorks]
> GO
> CREATE USER [test] FOR LOGIN [test]
> GO
> use [AdventureWorks]
> GO
> GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> GO
> GRANT IMPERSONATE ON USER:: dbo TO test;
> GO
> /*Now, open up a "file/new/DB Engine Query" and login with the test login*/
> exec as user = 'dbo'
> exec getDecryptedIDNumber
> /*This returns NULL values where it should show the decrypted data*/
>
>
|||Good enough, thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BD1F94F-E0F6-44EB-83FE-2C930BF1FF04@.microsoft.com...
> Hi Chris
> I could not get you sample to produce the effect you say, but then I
changed
> the procedure to open/close the keys. You should have the keys open for as
> short a time as possible
>
> CREATE PROCEDURE getDecryptedIDNumber
> WITH EXEC AS OWBER
> AS
> OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
> SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID
Number],
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS [Decrypted
ID
> Number]
> FROM HumanResources.Employee;
> CLOSE SYMMETRIC KEY SSN_Key_01;
> GO
> If this does not work you may want to post in
> microsoft.public.sqlserver.security
> A good source for encryption information is
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx will dop what
you[vbcol=seagreen]
> but signs the procedure instead.
>
> John
> "ChrisR" wrote:
Whenever[vbcol=seagreen]
the[vbcol=seagreen]
'dbo'"[vbcol=seagreen]
CHECK_POLICY=OFF[vbcol=seagreen]
login*/[vbcol=seagreen]

Wednesday, March 7, 2012

Enclosing a table create inside BEGIN END

The following SQL (after the "The SQL" marker) works fine. But I want to enclose it in a check to make sure that a previous script hasn't already created this object and its CRUD.

If I enclose it in a block like this...

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ZIPCODE]') AND type in (N'U'))
BEGIN

blah blah blah

END

It won't parse. It seems to me like this should be a no brainer, but it is hurting my brain.

The SQL...

CREATE TABLE [dbo].[ZIPCODE](
[Zip] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Latitude] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Longitude] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[County] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip_class] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_ZIPCODE] PRIMARY KEY CLUSTERED
(
[Zip] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/* Zip Code CRUD */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetZipLocationsWithinBounds]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetZipLocationsWithinBounds]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetZipLocationsByCityState]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetZipLocationsByCityState]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetZipLocation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetZipLocation]
GO
CREATE PROCEDURE [dbo].[GetZipLocationsWithinBounds]
@.TopLine float,
@.LeftLine float,
@.Bottomline float,
@.RightLine float
AS
SELECT * FROM ZIPCODE
WHERE
LATITUDE >= @.Bottomline AND
LATITUDE <= @.TopLine AND
LONGITUDE >= @.LeftLine AND
LONGITUDE <= @.RightLine
ORDER BY State, City ASC
GO

CREATE PROCEDURE [dbo].[GetZipLocationsByCityState]
@.City varchar(25),
@.State varchar(2)
AS
SELECT * FROM ZIPCODE
WHERE
City = @.City AND
State = @.State
ORDER BY State, City ASC
GO

CREATE PROCEDURE [dbo].[GetZipLocation]
@.ZipCode varchar(10)
AS
SELECT * FROM ZIPCODE
WHERE
Zip = @.ZipCode
GO

Hi there,

This error is caused by a GO keyword. The GO command force the SQL Interpreter to execute the previous part of your code block! so if the Interpreter try to execute your first create command your IF block make this error.

If your statements doesn't have any dependency to each other your simply solve this problem by removing the GO commands; But if there is some dependency you must break them in an independent part.

Babak Izadi
LotraSoft Ltd.

|||

You should not use GO inside your T-SQL Batch statement.

Go is not a T-SQL Statement. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server.

Remove all the GO statements & put it in your BEGIN .. END .. It will work ..

Sunday, February 26, 2012

EnableReportDesignClientDownload

Dear all
I would like to turn of the Report Builder Buttom in the Report Manager. It
worked with the following script out of msdn:
Class Sample
Public Shared Sub Main(ByVal prompt As String)
Dim rs As New ReportingService.ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim props(0) As [Property]
Dim setProp As New [Property]
setProp.Name = "EnableReportDesignClientDownload "
setProp.Value = prompt
props(0) = setProp
Try
rs.SetSystemProperties(props)
Catch ex As System.Web.Services.Protocols.SoapException
Console.Write(ex.Detail.InnerXml)
Catch e As Exception
Console.Write(e.Message)
End Try
End Sub 'Main
My problem is now, when I try to enable the Report Builder Buttom, this does
not work. Any idea why?
Thanks in advance for your answer,
Marc
--
Best regards,
MarcHello Marc,
Users must have permission to the Execute Report Definitions task (normally
obtained via the System Users role) for the Report Builder button to be
displayed.
See Home->Site Settings->Configure system-level role definitions->System
User->Execute Report Definitions
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Peter
it worked well thanks!
Best regards,
Marc|||Welcome!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Friday, February 24, 2012

Enable/Disable Job with SMO

I am trying to enable/disable a job using a using smo in a script task with the following code

Imports System.Object
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Agent
Imports Microsoft.SqlServer.Management.Smo.SqlSmoObject
Imports Microsoft.SqlServer.Management.Smo.SmoObjectBase
Imports Microsoft.SqlServer.Management.Smo.NamedSmoObject
Imports Microsoft.SqlServer.Management.Smo.Agent.Job
Imports Microsoft.SqlServer.Management.Smo.Agent.Jobserver
Imports Microsoft.SqlServer.Management.Smo.Agent.AgentObjectBase

Public Class ScriptMain

Public Sub Main()

Dim strJobserver As JobServer

Dim strJob As Job
Dim srv As Server
srv = New Server("conchango-vpc")

strJob = New Job(srv.JobServer, "test")

strJob.IsEnabled = True

strJob.Alter()

Dts.TaskResult = Dts.Results.Success
End Sub

End Class
and i get an error at the alter point.

when code is changed to using the following line
srv.Jobserver.Alter()
it runs successfully but no change is made to the job.
What am I missing?
Ifaka Enefe
Conchango

Your code is trying to create a new job called "test" instead of using the existing job.

Instead of:
strJob = New Job(srv.JobServer, "test")

Use:
strJob = srv.JobServer.Jobs("test")|||It worked. Thanks!|||Hi,
I've got the followinng inside a class library:

Server serv;

Job jb;

serv = new Server(this.strServerName);

jb = serv.JobServer.Jobs(this.strJobName);

if (strJobEnabledStatus=="True")

{

jb.IsEnabled = true;

}

else

{

jb.IsEnabled=false;

}

jb.Alter();

And it complains about

serv.JobServer.Jobs(this.strJobName)

saying "'Microsoft.SqlServer.Management.Smo.Agent.JobServer.Jobs' is a property but is used like a 'method'"

Any ideas? I aint much a developer I'm afraid so I'm at a bit of a loss here.

Thanks
Jamie|||s'all right. I've found it.

jb = serv.JobServer.Jobs[this.strJobName];

Must get my head out of VB mode Smile

-Jamie

Enable/Disable Job with SMO

Public Class ScriptMain

Public Sub Main()

Dim strJobserver As JobServer

Dim strJob As Job
Dim srv As Server
srv = New Server("conchango-vpc")

strJob = New Job(srv.JobServer, "test")

strJob.IsEnabled = True

strJob.Alter()

Dts.TaskResult = Dts.Results.Success
End Sub

End Class
and i get an error at the alter point.

when code is changed to using the following line
srv.Jobserver.Alter()
it runs successfully but no change is made to the job.
What am I missing?
Ifaka Enefe
Conchango

Your code is trying to create a new job called "test" instead of using the existing job.

Instead of:
strJob = New Job(srv.JobServer, "test")

Use:
strJob = srv.JobServer.Jobs("test")|||It worked. Thanks!|||Hi,
I've got the followinng inside a class library:

And it complains about

serv.JobServer.Jobs(this.strJobName)

saying "'Microsoft.SqlServer.Management.Smo.Agent.JobServer.Jobs' is a property but is used like a 'method'"

Any ideas? I aint much a developer I'm afraid so I'm at a bit of a loss here.

Thanks
Jamie|||s'all right. I've found it.

jb = serv.JobServer.Jobs[this.strJobName];

Must get my head out of VB mode Smile

-Jamie

enable TCPIP Protocols by script

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

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

Enable SQL Server Remote Connections via Script

Hi,
I am connecting to my SQL server 2005 database using a remote connection.
Can someone provide me with a SQL script which will enable connections
via TCP/IP to the database? (i.e. I'd like to do the step of going to
the configuration manager and enabling the TCP/IP protocol for the SQL
Server Instance using a SQL Script instead of doing through the UI).
Thanks in advance!
- ramaduHello Ramadu,
Thank you for posting in the MSDN newsgroup.
From your description, I understand that you're wondering how to configure
the SQL Server 2005 database instance's remote connectivity settings(the
allowed protocols) programmatically (through commandline or script file),
correct?
Based on my research, as for the remote connection setting, generally we
can configure it in the SQL Server 2005's "Surface Area Configuration" tool
which is a GUI component. However, it has a commandline version (SAC.EXE)
which can execute command based on a XML file, and the xml file can be
exported from an existing server and use the SAC.exe to
import the setting on another machine with SQL 2005.
#sac Utility
http://msdn2.microsoft.com/en-us/ms162800.aspx
And here is a web article which demonstrate on the most common
configuration tools in SQL SERVER 2005:
#SQL Server 2005 Management Tools
http://www.informit.com/guides/cont...seqNum=178&rl=1
Also, if you're familiar with WMI programming, you can have a look at the
new WMI provider interfaces provided in SQL SERVER 2005:
#WMI Provider for Configuration Management
http://msdn2.microsoft.com/en-us/library/ms180499.aspx
http://blogs.msdn.com/sql_protocols.../19/482840.aspx
Hope this helps.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Steven,
I'm not looking for a command line tool. I am rather looking for a
Stored Procedure / SQL Script which I can execute using the Query
Analyzer to enable remote connections.
The reason for this is that we are in the process of migrating from SQL
Server 2000 to SQL Server 2005 and in our application we provide a SQL
Script which out clients execute for doing updates to their database. I
wanted to include the enabling of remote connections in the same.
Hope you understood what I am looking for.
- ramadu
:
> Hello Ramadu,
> Thank you for posting in the MSDN newsgroup.
> From your description, I understand that you're wondering how to configure
> the SQL Server 2005 database instance's remote connectivity settings(the
> allowed protocols) programmatically (through commandline or script file),
> correct?
> Based on my research, as for the remote connection setting, generally we
> can configure it in the SQL Server 2005's "Surface Area Configuration" too
l
> which is a GUI component. However, it has a commandline version (SAC.EXE)
> which can execute command based on a XML file, and the xml file can be
> exported from an existing server and use the SAC.exe to
> import the setting on another machine with SQL 2005.
> #sac Utility
> http://msdn2.microsoft.com/en-us/ms162800.aspx
> And here is a web article which demonstrate on the most common
> configuration tools in SQL SERVER 2005:
> #SQL Server 2005 Management Tools
> http://www.informit.com/guides/cont...seqNum=178&rl=1
>
> Also, if you're familiar with WMI programming, you can have a look at the
> new WMI provider interfaces provided in SQL SERVER 2005:
> #WMI Provider for Configuration Management
> http://msdn2.microsoft.com/en-us/library/ms180499.aspx
> http://blogs.msdn.com/sql_protocols.../19/482840.aspx
> Hope this helps.
> Regards,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Study the registry. this setting is stored in the registry, so look at it be
fore and after you
modify using the tool. Then you can use xp_instance_regwrite to do the modif
ication from within SQL
Server. This xp isn't documented so use at your own risk, and a method which
it really preferable is
to ship with a CLR proc that you wrote and that does the registry modificati
ons for you. Another
thought is to have the CLR proc use SMO to do this instead of hacking the re
gistry directly.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ramadu" <tnr@.newsgroups.nospam> wrote in message news:%23NMu$7ujGHA.3304@.TK2MSFTNGP03.phx.
gbl...
> Hi Steven,
> I'm not looking for a command line tool. I am rather looking for a Stored
Procedure / SQL Script
> which I can execute using the Query Analyzer to enable remote connections.
> The reason for this is that we are in the process of migrating from SQL Se
rver 2000 to SQL Server
> 2005 and in our application we provide a SQL Script which out clients exec
ute for doing updates to
> their database. I wanted to include the enabling of remote connections in
the same.
> Hope you understood what I am looking for.
> - ramadu
> :|||Thanks for Tibor's informative input.
Hi Ramadu,
I'm sorry for the misunderstand, I originally think that you're wantting
some shell script(vbscript, jscript) code to run such task on commandline.
Actually, to enable remote connections in SQL Server 2005 it'll involve
multiple steps:
#How to configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/?id=914277
and not all of them are possible to do in pure T-SQL script.
1) Based on my research, as for the "Surface Area configuration" setting,
we need to use "SAC.exe" utility, and if we want to integrate this command
into T-SQL script, we can consider use the "xp_cmdshell" extender command
which can help execute external commandline utility(such as the SAC.exe).
You need to enable it through sp_configure in script since it is disabled
by default:
#xp_cmdshell
http://msdn.microsoft.com/library/e...jxo.asp?frame=t
rue
However, since SAC.exe will execute based on a xml file, it will still
require external resources(can not completely be done in T-SQL). BTW, do
you think it convenient that you put all such external resource file(such
as the xml import/export file) in a shared folder location so that it can
be conveniently referenced by commandline tool(through xp_cmdshell)?
2) As for enabling server protocol, there are serveral approaches, however,
most of them rely on some external programming interfaces, such as WMI or
.net framework SMO objects(new in SQL 2005). If none of them is possible
for your scenario, I'm afraid the only way is to manipulate the registry
directly (frankly speaking, this is not what I would prefer or recommend).
The server protocols setting is under the following registry path (specific
to each instance):
===========
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\MSSQL. 1\MSSQLServer\SuperSocketNetLib\Tcp\@.Ena
bled
===========
and there is some internal system SP which can help manipulate registry
values. e.g.
the below command enable the "TCP" protocol for the certain instance:
=============
EXEC master..xp_regwrite @.rootkey='HKEY_LOCAL_MACHINE',
@.key='SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp', @.value_name='Enabled',
@.type='REG_DWORD', @.value=1
===============
Here is a web article introduce some other such undocumented system
extended sps:
http://www.sql-server-performance.c..._procedures.asp
3) If there is also windows firewall enabled on the server, here is a kb
article discussing on programmatically open firewall ports for sqlserver:
#How to use a script to programmatically open ports for SQL Server to use
on systems that are running Windows XP Service Pack 2
http://support.microsoft.com/kb/839980
Hope this also helps.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Thanks Steven! I will look into these.
- Sri
Steven Cheng[MSFT] wrote:
> Thanks for Tibor's informative input.
> Hi Ramadu,
> I'm sorry for the misunderstand, I originally think that you're wantting
> some shell script(vbscript, jscript) code to run such task on commandline.
> Actually, to enable remote connections in SQL Server 2005 it'll involve
> multiple steps:
> #How to configure SQL Server 2005 to allow remote connections
> http://support.microsoft.com/?id=914277
> and not all of them are possible to do in pure T-SQL script.
> 1) Based on my research, as for the "Surface Area configuration" setting,
> we need to use "SAC.exe" utility, and if we want to integrate this command
> into T-SQL script, we can consider use the "xp_cmdshell" extender command
> which can help execute external commandline utility(such as the SAC.exe).
> You need to enable it through sp_configure in script since it is disabled
> by default:
> #xp_cmdshell
> [url]http://msdn.microsoft.com/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp?frame=t[/ur
l]
> rue
> However, since SAC.exe will execute based on a xml file, it will still
> require external resources(can not completely be done in T-SQL). BTW, do
> you think it convenient that you put all such external resource file(such
> as the xml import/export file) in a shared folder location so that it can
> be conveniently referenced by commandline tool(through xp_cmdshell)?
>
> 2) As for enabling server protocol, there are serveral approaches, however
,
> most of them rely on some external programming interfaces, such as WMI or
> .net framework SMO objects(new in SQL 2005). If none of them is possible
> for your scenario, I'm afraid the only way is to manipulate the registry
> directly (frankly speaking, this is not what I would prefer or recommend).
> The server protocols setting is under the following registry path (specifi
c
> to each instance):
> ===========
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
> Server\MSSQL. 1\MSSQLServer\SuperSocketNetLib\Tcp\@.Ena
bled
> ===========
> and there is some internal system SP which can help manipulate registry
> values. e.g.
> the below command enable the "TCP" protocol for the certain instance:
> =============
> EXEC master..xp_regwrite @.rootkey='HKEY_LOCAL_MACHINE',
> @.key='SOFTWARE\Microsoft\Microsoft SQL
> Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp', @.value_name='Enabled',
> @.type='REG_DWORD', @.value=1
> ===============
> Here is a web article introduce some other such undocumented system
> extended sps:
> http://www.sql-server-performance.c..._procedures.asp
>
> 3) If there is also windows firewall enabled on the server, here is a kb
> article discussing on programmatically open firewall ports for sqlserver:
> #How to use a script to programmatically open ports for SQL Server to use
> on systems that are running Windows XP Service Pack 2
> http://support.microsoft.com/kb/839980
> Hope this also helps.
> Regards,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)|||Thanks for your response Sri,
Hope that will help you resolve the problem. If you meet any further
problem, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Monday, March 19, 2012

Encryptin stored procedures

Encrypted object is not transferable

Encrypted object is not transferable

Sunday, March 11, 2012

Encrypt sproc still returns NULL's to non DBO's.

Encrypt sproc still returns NULL's to non DBO's.

Encrypt sproc still returns NULL's to non DBO's.

Wednesday, March 7, 2012

Enclosing a table create inside BEGIN END

Sunday, February 26, 2012

EnableReportDesignClientDownload

Friday, February 24, 2012

Enable/Disable Job with SMO

Enable/Disable Job with SMO

enable TCPIP Protocols by script

Enable SQL Server Remote Connections via Script

Enable SQL Server Remote Connections via Script

Enable SQL Server Remote Connections via Script

Empty Column

Blog Archive

About Me