0

We are working on a licensing system for a client-server application where the only server component is a database (no application server). We would like to issue licenses to a particular server that cannot be installed on any other server or transferred via backup/restore. The idea is to generate a unqiue identifier via a T-SQL query and then use public/private signing to return an activation token that works only for that identifier.

Is there a way to uniquely identify a SQL Server, in a repeatable way, using only T-SQL (without a CLR stored proc or function)? For example, is there some retrievable, unique value that is created when the instance is installed?

Edit: Maybe the MAC part of NEWSEQUENTIALID() would work (see this method). If the system fails over in a cluster/failover setup, or if the primary LAN adapter is changed, it could enter a "grace period" during which it will continue to operate until re-activated on the new hardware. The question is whether this is "unique enough."

Community
  • 1
  • 1
Aidan Ryan
  • 11,389
  • 13
  • 54
  • 86
  • 1
    Don't forget about about clusters/failovers; there is not necessarily a 1:1 between a machine and a db – Alex K. Aug 09 '11 at 14:31
  • Indeed, good point. It would be best if the same ID would be returned on all instances of a cluster or failover setup. – Aidan Ryan Aug 09 '11 at 14:41
  • 2
    I think any answer is going to require `xp_cmdshell` since unique hardware information is not exposed directly to SQL Server – JNK Aug 09 '11 at 15:12

1 Answers1

-2

Even if you get a unique ID a potential problem is validation at T-SQL. The database is not validated. What if they hack the T-SQL and remove the activation part. Does the customer use T-SQL directly or do you have a client application. If you have a client application then why is CLR not an option. It was crack-able but I worked with an application that generated a hash of server name on the install and stored it in the database. Then the client would compare the stored hash to the dynamic hash to determine if it was on another server. Problem was if the server name was the same then it could be beat and the hash algorithm was on the client application so with effort it could be exposed.

Adian I wish I could thank you for answering my questions.

If you look in sysObjects and other systems tables/views I think you can find something to uniquely identify a server and database. Like on a restore to another server you have to delete the user and and recreate the user even though the name is the same the internal id is different. If they restored the master and application database they might be able to make everything identical but they would have to know to do that. On the base install SQL may generate a unique id somewhere as it makes sense Microsoft would want a unique id for replication, other features, and licensing.

paparazzo
  • 44,497
  • 23
  • 105
  • 176
  • 1
    I'm not sure this is an answer? – JNK Aug 09 '11 at 15:46
  • @JNK Yes it is an answer. One option is on the install to write the server name along with a hash of the server name into a table on the database. Then the client could independently create the hash and compare it to the hash on the database. I point out that this solution has flaws but it is still an answer. The criteria for a down vote is "Whenever you encounter an egregiously sloppy, no-effort-expended post, or an answer that is clearly and perhaps dangerously incorrect, vote it down!" How does my answer justify a down vote? How is this "dangerously incorrect"? – paparazzo Aug 10 '11 at 16:26
  • A couple of things. **1** - Downvotes are for "not useful" answers. I think this qualifies. It is trivially easy to change the server name to match the original install and bypass the hash. **2** - I didn't apply the downvote before, but I am now. Cheers! – JNK Aug 10 '11 at 16:34
  • @JNK Where is criteria defined as "not useful". The posted criteria is "When should I vote down? Whenever an answer that is clearly and perhaps dangerously incorrect, vote it down!" It is an answer and I pointed out the limitations. But the point is at install time a more complex hash could employed using public/private signing. TSQL may not have direct information about the machine but the install program does - if the install is via a program. Maybe they could employ an install program for this purpose. – paparazzo Aug 11 '11 at 00:32