|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Encrypt function in SQL2Kon different servers. Both servers are running SQL2KSP4 but the script below returns: 0x6100 and 0xB0ED on one server (this is what I would expect). But on the other server it returns 0x6100 and 0x6100 - so the encryption is not happening. Does anyone know why? Here is the script: create procedure dbo.sp_pwdtest @pwd nvarchar(256), @retval varbinary(256) output as set @retval = encrypt(@pwd) select @retval go select encrypt('a') go declare @return varbinary(256) exec dbo.sp_pwdtest 'a', @return go IF OBJECT_ID ('sp_pwdtest') IS NOT NULL DROP PROCEDURE sp_pwdtest go Are you sure you are using SQL Server 2005 release version? There isn't an
encrypt function in TSQL. -- Show quoteThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "DBA72" <DB***@discussions.microsoft.com> wrote in message news:9C5D7521-F53F-4D69-B4D7-76E45CE29269@microsoft.com... > Has anyone played around with this function? I have different results with > it > on different servers. Both servers are running SQL2KSP4 but the script > below > returns: 0x6100 and 0xB0ED on one server (this is what I would expect). > But > on the other server it returns 0x6100 and 0x6100 - so the encryption is > not > happening. Does anyone know why? > > Here is the script: > create procedure dbo.sp_pwdtest @pwd nvarchar(256), @retval varbinary(256) > output > as > set @retval = encrypt(@pwd) > select @retval > go > select encrypt('a') > go > declare @return varbinary(256) > exec dbo.sp_pwdtest 'a', @return > go > IF OBJECT_ID ('sp_pwdtest') IS NOT NULL > DROP PROCEDURE sp_pwdtest > go Hello Roger,
No this is in SQL Server 2000 SP4. Show quote "Roger Wolter[MSFT]" wrote: > Are you sure you are using SQL Server 2005 release version? There isn't an > encrypt function in TSQL. > > -- > This posting is provided "AS IS" with no warranties, and confers no rights. > Use of included script samples are subject to the terms specified at > http://www.microsoft.com/info/cpyright.htm > > "DBA72" <DB***@discussions.microsoft.com> wrote in message > news:9C5D7521-F53F-4D69-B4D7-76E45CE29269@microsoft.com... > > Has anyone played around with this function? I have different results with > > it > > on different servers. Both servers are running SQL2KSP4 but the script > > below > > returns: 0x6100 and 0xB0ED on one server (this is what I would expect). > > But > > on the other server it returns 0x6100 and 0x6100 - so the encryption is > > not > > happening. Does anyone know why? > > > > Here is the script: > > create procedure dbo.sp_pwdtest @pwd nvarchar(256), @retval varbinary(256) > > output > > as > > set @retval = encrypt(@pwd) > > select @retval > > go > > select encrypt('a') > > go > > declare @return varbinary(256) > > exec dbo.sp_pwdtest 'a', @return > > go > > IF OBJECT_ID ('sp_pwdtest') IS NOT NULL > > DROP PROCEDURE sp_pwdtest > > go > > > Roger and DBA72
The original poster mentioned SQL2K in the subject, not 2005. I assume the function in question is the undocumented ENCRYPT function, which is gone in SQL Server 2005. I don't know why you would expect (or get) 0xB0ED from encrypt('a') outside of the stored procedure, but 0x6100 from encrypt('a') inside the stored procedure. I'd expect to get the same thing in both result sets, 0x6100. See http://groups.google.com/groups/search?q=%22Encrypted+data+problem+in+SQL+upgrade%22 Steve Kass Drew University www.stevekass.com Show quote "Roger Wolter[MSFT]" <rwol***@online.microsoft.com> wrote in message news:u9SINl0wGHA.560@TK2MSFTNGP05.phx.gbl... > Are you sure you are using SQL Server 2005 release version? There isn't an encrypt function in TSQL. > > -- > This posting is provided "AS IS" with no warranties, and confers no rights. > Use of included script samples are subject to the terms specified at > http://www.microsoft.com/info/cpyright.htm > > "DBA72" <DB***@discussions.microsoft.com> wrote in message news:9C5D7521-F53F-4D69-B4D7-76E45CE29269@microsoft.com... >> Has anyone played around with this function? I have different results with it >> on different servers. Both servers are running SQL2KSP4 but the script below >> returns: 0x6100 and 0xB0ED on one server (this is what I would expect). But >> on the other server it returns 0x6100 and 0x6100 - so the encryption is not >> happening. Does anyone know why? >> >> Here is the script: >> create procedure dbo.sp_pwdtest @pwd nvarchar(256), @retval varbinary(256) >> output >> as >> set @retval = encrypt(@pwd) >> select @retval >> go >> select encrypt('a') >> go >> declare @return varbinary(256) >> exec dbo.sp_pwdtest 'a', @return >> go >> IF OBJECT_ID ('sp_pwdtest') IS NOT NULL >> DROP PROCEDURE sp_pwdtest >> go > > DBA72 wrote:
> Has anyone played around with this function? I have different results with it I get 0x6100 in both cases. Why did you expect different results from> on different servers. Both servers are running SQL2KSP4 but the script below > returns: 0x6100 and 0xB0ED on one server (this is what I would expect). But > on the other server it returns 0x6100 and 0x6100 - so the encryption is not > happening. Does anyone know why? > the same function? ENCRYPT is an undocumented feature so results can't be guaranteed. As Roger mentioned this function is gone from 2005, which is a good reason to stop using it now. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- As far as I know, this function only does "what it is supposed to" when it is
called from a dbo owned stored procedure. Otherwise it will just convert the data to varbinary. But that still doesn't answer my question as to why on two different servers with the same sql version and called from a dbo owned stored proc, the function encrypts the data on one server but not on the other. Show quote "David Portas" wrote: > DBA72 wrote: > > Has anyone played around with this function? I have different results with it > > on different servers. Both servers are running SQL2KSP4 but the script below > > returns: 0x6100 and 0xB0ED on one server (this is what I would expect). But > > on the other server it returns 0x6100 and 0x6100 - so the encryption is not > > happening. Does anyone know why? > > > > I get 0x6100 in both cases. Why did you expect different results from > the same function? > > ENCRYPT is an undocumented feature so results can't be guaranteed. As > Roger mentioned this function is gone from 2005, which is a good reason > to stop using it now. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > >  |
|||||||||||||||||||||||
Other interesting topics