Answered by:
Encrypt @@servername returns only first character

Question
-
I’m thinking of creating a “licensing system” for our SQL server based solution, preventing the solution from being copied from server to server. I was thinking to use an encrypted proc that takes the servername where the solution is installed and encrypt it. The system admin get this encrypted key, send it to us, we decrypt, add some licensing info, and encrypt a new “license key”, that we send to the customer. The system admin saves the licence key in a table and we verify in our other procs that the license key and the servername is valid.
The first proc should do something similar to this;
CREATE TABLE #server
(servername varbinary(8000) NOT NULL)
go
INSERT INTO #server (servername)
SELECT EncryptByPassPhrase('password', @@SERVERNAME)
SELECT servername
FROM #server
SELECT cast (DecryptByPassPhrase('password', servername) AS varchar(500)) FROM #server
DROP TABLE #server
The problem is the decrypted server name only returns the first character, not the whole server name. If I take away @@SERVERNAME and put in ‘SERVERNAME’, then it works (i.e. when using a string instead of a system variable).
Any ideas?
Anyone having a better solution for creating a simple "licensing system"?
/Erik
Friday, September 5, 2008 4:23 PM
Answers
-
Do a convert(varchar(128), @@SERVERNAME) and it will work. @@SERVERNAME returns a SYSNAME datatype which is equivalent to a nvarchar(128).
Code SnippetCREATE
TABLE #server(
servername varbinary(8000) NOT NULL)INSERT
INTO #server (servername)SELECT
EncryptByPassPhrase('password', convert(varchar(128), @@SERVERNAME))SELECT
servernameFROM
#serverSELECT
cast (DecryptByPassPhrase('password', servername) AS varchar(128)) FROM #serverDROP
TABLE #serverEDIT;
Or you can cast back to sysname and it works:
Code SnippetCREATE
TABLE #server(
servername varbinary(8000) NOT NULL)INSERT
INTO #server (servername)SELECT
EncryptByPassPhrase('password', @@SERVERNAME)SELECT
servernameFROM
#serverSELECT
cast (DecryptByPassPhrase('password', servername) AS sysname) FROM #serverDROP
TABLE #serverFriday, September 5, 2008 5:21 PM
All replies
-
Do a convert(varchar(128), @@SERVERNAME) and it will work. @@SERVERNAME returns a SYSNAME datatype which is equivalent to a nvarchar(128).
Code SnippetCREATE
TABLE #server(
servername varbinary(8000) NOT NULL)INSERT
INTO #server (servername)SELECT
EncryptByPassPhrase('password', convert(varchar(128), @@SERVERNAME))SELECT
servernameFROM
#serverSELECT
cast (DecryptByPassPhrase('password', servername) AS varchar(128)) FROM #serverDROP
TABLE #serverEDIT;
Or you can cast back to sysname and it works:
Code SnippetCREATE
TABLE #server(
servername varbinary(8000) NOT NULL)INSERT
INTO #server (servername)SELECT
EncryptByPassPhrase('password', @@SERVERNAME)SELECT
servernameFROM
#serverSELECT
cast (DecryptByPassPhrase('password', servername) AS sysname) FROM #serverDROP
TABLE #serverFriday, September 5, 2008 5:21 PM -
Not sure why it's only returning the first letter, I ran it and the same thing happened. However, it works with a local variable
Code SnippetCREATE
TABLE #server(
servername varbinary(8000) NOT NULL)go
DECLARE
@Server VARCHAR(100)SET
@Server = @@ServerNameINSERT
INTO #server (servername)SELECT
EncryptByPassPhrase('password', @Server)SELECT
servernameFROM
#serverSELECT
cast (DecryptByPassPhrase('password', servername) AS varchar(500)) FROM #serverDROP
TABLE #server- Proposed as answer by clorkster Tuesday, October 8, 2013 4:47 PM
Friday, September 5, 2008 5:22 PM -
try:
Code SnippetCREATE TABLE #server
(servername varbinary(8000) NOT NULL)
go
INSERT INTO #server (servername)
SELECT EncryptByPassPhrase('password', @@SERVERNAME)
SELECT servername
FROM #server
SELECT cast(DecryptByPassPhrase('password', servername) as nvarchar) FROM #server
DROP TABLE #server
Friday, September 5, 2008 5:24 PM -
DECLARE @Server VARCHAR(100)
SET @Server = @@ServerName
INSERT INTO #server (servername)
SELECT EncryptByPassPhrase('password', @Server)
I know that this is a VERY old thread, however, I ran into a similar problem and only found this thread out there about it. I was able to solve the problem by simple declaring the variable in the stored procedure as suggested by Jay.
Tuesday, October 8, 2013 4:46 PM