Get the SQL Server Unique ID of the Server

Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
EdwardIf you mean you want to find the name of the Windows machine that the
SQL instance is running on then you could use "SELECT
SERVERPROPERTY('MachineName')" or if you're after the name of the SQL
instance itself then you could use "SELECT
SERVERPROPERTY('ServerName')", which will return the Windows machine
name and the SQL instance name.
*mike hodgson*
http://sqlnerd.blogspot.com
Edward Low wrote:

>Hi All,
>Is there any ID / Key which identify the server of the SQL Server
>installation uniquely? I need this ID to make sure the database installed i
n
>a SQL server can't be transferred to other SQL Server, for licencing issues
.
>Or, is there other ways to achieve this?
>
>Thanks & Regards,
>Edward
>
>|||Hi mike,
thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a uniqu
e, mean someone can have the same servername on other machine. I need someth
ing unique like the hard drive serial no which is unique in each machine. ca
n we get this information from sql server?
Best regards,
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:e7vcOyvfGHA.4304@.
TK2MSFTNGP05.phx.gbl...
If you mean you want to find the name of the Windows machine that the SQL in
stance is running on then you could use "SELECT SERVERPROPERTY('MachineName'
)" or if you're after the name of the SQL instance itself then you could use
"SELECT SERVERPROPERTY('ServerName')", which will return the Windows machin
e name and the SQL instance name.
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
Edward|||For SQL Server 2005 you could write an Unsafe CLR procedure which can get th
e Hard drive serial number or any other metric tha you want.
However!? What happens if said hard drive breaks and the server has to be
re-built with a different hard drive and a database backup restored?
Regards
Colin Dawson
www.cjdawson.com
"Edward Low" <wc_low@.hotmail.com> wrote in message news:ORW6yDwfGHA.5088@.TK2
MSFTNGP02.phx.gbl...
Hi mike,
thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a uniqu
e, mean someone can have the same servername on other machine. I need someth
ing unique like the hard drive serial no which is unique in each machine. ca
n we get this information from sql server?
Best regards,
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:e7vcOyvfGHA.4304@.
TK2MSFTNGP05.phx.gbl...
If you mean you want to find the name of the Windows machine that the SQL in
stance is running on then you could use "SELECT SERVERPROPERTY('MachineName'
)" or if you're after the name of the SQL instance itself then you could use
"SELECT SERVERPROPERTY('ServerName')", which will return the Windows machin
e name and the SQL instance name.
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
Edward|||If you could get the Windows domain name (there's no real kosher way
that I know of to do this purely with SQL (you'd have to shell out to
DOS with xp_cmdshell (yuk), like
exec xp_cmdshell 'echo %USERDOMAIN%'
)) then the domain/machine/instance has to be unique (although you could
argue that different unrelated companies, or individuals, could create
domains with the same name).
Alternately I think the ProductID (stored in the registry under
HKLM\SOFTWARE\Microsoft\Windows\CurrentV
ersion\ProductId) is supposed to
be unique for a Windows installation, so that, combined with the
instance name, ought to be a unique SQL instance. However, once again,
there's no real kosher way to read registry keys. In SQL 2000 you can
use the proc xp_regread, like this:
declare @.ProductId varchar(100)
exec xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows\CurrentVersi
on',
'ProductId',
@.ProductId OUTPUT
select @.ProductId
but this is an undocumented proc and in SQL 2005 Microsoft have changed
its behaviour (the permissions around what keys it can and cannot read)
and so it probably won't work in SQL 2005 (although I just tried it on
both a Dev Edition & Ent Edition of SQL 2005 SP1 (x64) and it worked on
both but the trusted login I used was a local admin so that might have
skewed the result a little). But, in any case, it's an undocumented
proc so you probably shouldn't use it (if you want it to continue
working in future versions of SQL Server) and also, if the SQL instance
is installed on a cluster you'd get a different ProductId value
depending on which node the SQL instance happened to be running on at
the time. There's a couple other reg keys you might be able to read to
get a unique ID (like the 180 byte binary SQL setup checksum) but they
all still have the "read from the registry" problem (and on a cluster
reading from the SQL branches of the registry is very messy because
you've got to look up the installed instances, get the one you want,
look up which reg key that instance has its values stored in, go to that
reg key and get the value you're after).
There's no real nice way I can think of to tie a DB to a specific SQL
instance, not with T-SQL code from within a SQL Server session anyway
(you could store a hash, generated outside of a SQL session like in VB
or C# code for example, of some unique attributes of the SQL
installation, like domainname/servername/instancename or
IPaddress/TCPportnumber but those attributes may, validly, change over
time...).
*mike hodgson*
http://sqlnerd.blogspot.com
Edward Low wrote:
> Hi mike,
> thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a
> unique, mean someone can have the same servername on other machine. I
> need something unique like the hard drive serial no which is unique in
> each machine. can we get this information from sql server?
> Best regards,
> Edward
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:e7vcOyvfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> If you mean you want to find the name of the Windows machine that
> the SQL instance is running on then you could use "SELECT
> SERVERPROPERTY('MachineName')" or if you're after the name of the
> SQL instance itself then you could use "SELECT
> SERVERPROPERTY('ServerName')", which will return the Windows
> machine name and the SQL instance name.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Edward Low wrote:
>|||Hi Mike,
Thanks for your explanation.
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:uOGY$d6fGHA.5092@.
TK2MSFTNGP04.phx.gbl...
If you could get the Windows domain name (there's no real kosher way that I
know of to do this purely with SQL (you'd have to shell out to DOS with xp_c
mdshell (yuk), like
exec xp_cmdshell 'echo %USERDOMAIN%'
)) then the domain/machine/instance has to be unique (although you could arg
ue that different unrelated companies, or individuals, could create domains
with the same name).
Alternately I think the ProductID (stored in the registry under HKLM\SOFTWAR
E\Microsoft\Windows\CurrentVersion\Produ
ctId) is supposed to be unique for a
Windows installation, so that, combined with the instance name, ought to be
a unique SQL instance. However, once again, there's no real kosher way to
read registry keys. In SQL 2000 you can use the proc xp_regread, like this:
declare @.ProductId varchar(100)
exec xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows\CurrentVersi
on',
'ProductId',
@.ProductId OUTPUT
select @.ProductId
but this is an undocumented proc and in SQL 2005 Microsoft have changed its
behaviour (the permissions around what keys it can and cannot read) and so i
t probably won't work in SQL 2005 (although I just tried it on both a Dev Ed
ition & Ent Edition of SQL 2005 SP1 (x64) and it worked on both but the trus
ted login I used was a local admin so that might have skewed the result a li
ttle). But, in any case, it's an undocumented proc so you probably shouldn'
t use it (if you want it to continue working in future versions of SQL Serve
r) and also, if the SQL instance is installed on a cluster you'd get a diffe
rent ProductId value depending on which node the SQL instance happened to be
running on at the time. There's a couple other reg keys you might be able
to read to get a unique ID (like the 180 byte binary SQL setup checksum) but
they all still have the "read from the registry" problem (and on a cluster
reading from the SQL branches of the registry is very messy because you've g
ot to look up the installed instances, get the one you want, look up which r
eg key that instance has its values stored in, go to that reg key and get th
e value you're after).
There's no real nice way I can think of to tie a DB to a specific SQL instan
ce, not with T-SQL code from within a SQL Server session anyway (you could s
tore a hash, generated outside of a SQL session like in VB or C# code for ex
ample, of some unique attributes of the SQL installation, like domainname/se
rvername/instancename or IPaddress/TCPportnumber but those attributes may, v
alidly, change over time...).
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi mike,
thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a uniqu
e, mean someone can have the same servername on other machine. I need someth
ing unique like the hard drive serial no which is unique in each machine. ca
n we get this information from sql server?
Best regards,
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:e7vcOyvfGHA.4304@.
TK2MSFTNGP05.phx.gbl...
If you mean you want to find the name of the Windows machine that the SQL in
stance is running on then you could use "SELECT SERVERPROPERTY('MachineName'
)" or if you're after the name of the SQL instance itself then you could use
"SELECT SERVERPROPERTY('ServerName')", which will return the Windows machin
e name and the SQL instance name.
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
Edward