Get a server unique identifier

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get a server unique identifier

Author

Topic

brace77
Starting Member

25 Posts

Posted - 2009-02-12 : 06:40:29

Hello,

is there a way to get from SQL Server a unique identifier?

I need this for license check. My application uses SQL Server and I give an activation key that is generated using that unique number. This means that the activation key will enable the user to launch the application only if he is connecting to the server he paid the license for.

At present the trick I use is this: I created a DLL that reads the server (where SQL Server is installed) Hard Disk serial number and I wrote a Stored Function that calls that dll.

But this method is not bullet proof and moreover on SQL Server 2008 I have some problems.

It would be better and easier for me to directly call some Stored Function or to retrieve some unique number that is generated once SQL Server is installed.

Is it possible?

Is there such a function or a similar trick (like some particular field in systables)?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 06:43:40

NEWID()
NEWSEQUENTIALID()

E 12°55'05.63"
N 56°04'39.26"

brace77
Starting Member

25 Posts

Posted - 2009-02-12 : 06:56:03

Hi, thanks for the quick reply.

No, I need a Machine Identifier.

Let's imagine I sell my software to you.

To be sure you are not going to install it more than one server I give you an activation key that contains your name and your hard disk serial number.

Example:
Name: Peso
HardDisk ID: 12341234

So the activation key will be something like <user>Peso<ID>12341234 (encrypted of course!)

So 12341234 is a number that says "this is Peso server". It is a number you cannot control, it is the result of a DLL call to a windows API.

To make it simple I would like to know if there is a sql server funcion that returns a similar ID, I don't need an hard disk serial number, I need some number that says "this is Peso server", could be for example Mac Address (but I've been told this is not possible) but any other value is ok.

Can you help me? Is my question clear?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 07:39:40

See here how to get IP address out of machine.
The use for cmd_shell (with findstr) can also be applied to mac address instead of ip address.
http://weblogs.sqlteam.com/peterl/archive/2008/07/16/How-to-get-IP-address.aspx

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 07:50:09

[code]CREATE TABLE #IP
(
data VARCHAR(256)
)

INSERT #IP
EXEC master..xp_cmdshell 'ipconfig /all | findstr "Physical Address"'

SELECT SUBSTRING(data, 40, 17) AS MacAddress
FROM #IP
WHERE data LIKE '%' + LEFT(REPLICATE('[0-9a-f][0-9a-f]-', 6), 101) + '%'

DROP TABLE #IP[/code]

E 12°55'05.63"
N 56°04'39.26"

brace77
Starting Member

25 Posts

Posted - 2009-02-12 : 09:45:24

ok, the trick could be but useful but I see that in Italian it doesn't work, I mean it works but I have to modify the SUBSTRING parameters in order to locate the MAC address string... I'll use @@SERVERNAME as suggested here:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/400ff82f-de00-465c-a5ed-df3f4d254a4c

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 09:51:23

You don't need the findstr part! It's only there to speed things up...


CREATE TABLE	#IP
		(
			data VARCHAR(256)
		)

INSERT	#IP
EXEC	master..xp_cmdshell 'ipconfig /all'

SELECT	SUBSTRING(data, 40, 17) AS MacAddress
FROM	#IP
WHERE	data LIKE '%' + LEFT(REPLICATE('[0-9a-f][0-9a-f]-', 6), 101) + '%'

DROP TABLE #IP

E 12°55'05.63"
N 56°04'39.26"

brace77
Starting Member

25 Posts

Posted - 2009-02-13 : 09:02:44

Hello.

Yes, anyway I read that Extended Stored Procedures (to whom xm_cmdshell belongs) are deprecated from Microsoft.

So you have any idea whether this "deprectaion" applies to xp_cmdshell too?

So probably choosing another way to do it is better...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-13 : 09:04:50

Or maybe the extended procedure deprecation is for user-supplied DLLs?

E 12°55'05.63"
N 56°04'39.26"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-13 : 10:07:50

If you do go with your @@servername, I would suggest instead using one of these:

serverproperty('machinename')
serverproperty('instancename')
serverproperty('servername')

I think @@server can be null (or wrong) because the value comes from sysServers. (unlikely but possible)

Be One with the Optimizer
TG

brace77
Starting Member

25 Posts

Posted - 2009-02-17 : 03:16:06

Thanks for the suggestions, I am considering an other idea, it is not a ServerID, but a DataBase Id, anyway for my licensing purpose it is ok (since I license per DB).

The idea is to use

sp_helpdb 'DBNAME'

this returns "created", an nvarchar(9) field

This works perfectly (because creation date, combined with something else like dbname or server name, can give a kind of unique identifier, or at least almost unique, that is what I am looking for), but there is a problem, "created" is a string like "jan 12 2006" on an english server, "gen 12 2006" on an italian one.

It is not clear to me which is the language setting that influences this result. Is the language in which SQL Server is installed? In other words: once the server is instaslled (or the db created) is this string unique or can depend from some client settings?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources