locked
Encrypt @@servername returns only first character RRS feed

  • 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 Snippet

    CREATE TABLE #server

    (servername varbinary(8000) NOT NULL)

     

    INSERT INTO #server (servername)

    SELECT EncryptByPassPhrase('password', convert(varchar(128), @@SERVERNAME))

     

    SELECT servername

    FROM #server

     

    SELECT cast (DecryptByPassPhrase('password', servername) AS varchar(128)) FROM #server

     

    DROP TABLE #server

     

     

    EDIT;

     

    Or you can cast back to sysname and it works:

     

    Code Snippet

    CREATE TABLE #server

    (servername varbinary(8000) NOT NULL)

     

    INSERT INTO #server (servername)

    SELECT EncryptByPassPhrase('password', @@SERVERNAME)

     

    SELECT servername

    FROM #server

     

    SELECT cast (DecryptByPassPhrase('password', servername) AS sysname) FROM #server

     

    DROP TABLE #server

     

     

    Friday, 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 Snippet

    CREATE TABLE #server

    (servername varbinary(8000) NOT NULL)

     

    INSERT INTO #server (servername)

    SELECT EncryptByPassPhrase('password', convert(varchar(128), @@SERVERNAME))

     

    SELECT servername

    FROM #server

     

    SELECT cast (DecryptByPassPhrase('password', servername) AS varchar(128)) FROM #server

     

    DROP TABLE #server

     

     

    EDIT;

     

    Or you can cast back to sysname and it works:

     

    Code Snippet

    CREATE TABLE #server

    (servername varbinary(8000) NOT NULL)

     

    INSERT INTO #server (servername)

    SELECT EncryptByPassPhrase('password', @@SERVERNAME)

     

    SELECT servername

    FROM #server

     

    SELECT cast (DecryptByPassPhrase('password', servername) AS sysname) FROM #server

     

    DROP TABLE #server

     

     

    Friday, 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 Snippet

    CREATE TABLE #server

    (servername varbinary(8000) NOT NULL)

    go

    DECLARE @Server VARCHAR(100)

    SET @Server = @@ServerName

    INSERT INTO #server (servername)

    SELECT EncryptByPassPhrase('password', @Server)

    SELECT servername

    FROM #server

    SELECT cast (DecryptByPassPhrase('password', servername) AS varchar(500)) FROM #server

    DROP TABLE #server

     

     

    • Proposed as answer by clorkster Tuesday, October 8, 2013 4:47 PM
    Friday, September 5, 2008 5:22 PM
  • try:

     

    Code Snippet

    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 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