identifying instance/installation

Question

identifying instance/installation

Jim Zippeltoff

Valued Member

Points: 53
More actions
September 12, 2007 at 10:02 am

#197828

Hi,
I'm searching for a information which I could use to identify a sql server instance from another one.
This information should be possible to get with a query which does not need any special permissions (public) and as far as I can see, it should exists in the master database.
Using xp_cmdshell is not an option as it is disabled by default on SQL Server 2005.
select @@servername I am already aware of, looking for something else which is not so
It would also be great if this information could be found on both, sql server 2000 and 2005. It does not have to be the same information or the query syntax does not have to be the same.
any ideas?
thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

because there is no central source which captures which servers exist, you can't really query it.

you can ask the operating system to poll all the servers that are advertising themselves on the network using OSQL, but that's no availalble to PUBLIC users....and to get the results in a TSQL, you need xp_cmdshell.

to find the rest of the servers, that don't advertise their existance, you need to use a tool like SQLRecon, which uses lots of different methods to find all servers on a network.

once you have a list of servers, then you have to connect to each, and query @@version for each server you found to find out if they are SQLK or SQL 2005, swhich service pack, names of databases, etc.

here's how an admin can do it:

CREATE PROCEDURE dbo.ListLocalServers

AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #servers(sname VARCHAR(255))

INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'

-- play with ISQL -L too, results differ slightly

DELETE #servers WHERE sname='Servers:'

SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'

DROP TABLE #servers

END

EXEC ListLocalServers

results:

(local)

ASTRAH

AURORA

BELLE

etc....

no if you want to identify just the instances on a server you are connected to, I think you can read teh registry to get the list, but again, reading teh registry is not availalble to PUBLIC.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

mine has (default),SQL2005 and SQLEXPRESS as three keys in that list on my dev machine

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jim Zippeltoff Valued Member Points: 53 More actions · Answer 2

I'm just trying to find some unique or almost unique information from the master database, nothing more special.

The server name is already known and the connection + authentication is done, the need is just to differ the installation from another installation. (the servers are not in the same network etc)

I'm just unable to find one which I'd be happy with.

Something like ##MS_ServiceMasterKey##, but that one is too easy to change.

Querying the registry is not an options as you said.

Lowell SSC Guru Points: 323518 More actions · Answer 3

As i understand it, the database is it's own self contained unit, and doesn't have info about other instances that exist;

There's a few metadata functions, like SELECT @@SERVERNAME,@@SERVICENAME,@@VERSION , which give you machine name and instance name and stuff, but no information about other potential instances. I'm sure you aready evaluated those and discarded them.

Sorry I couldn't help.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jim Zippeltoff Valued Member Points: 53 More actions · Answer 4

yes, I'm aware of those and I tried to emphasize that I don't need information about other instances, I just want to differ to instances from each other. (not in a single query).

I just want to find something unique from a single master database, something which most likely cannot be found from another master database!

noeld SSC Guru Points: 96590 More actions · Answer 5

noeld

SSC Guru

Points: 96590

September 12, 2007 at 2:38 pm

#733277

SELECT SERVERPROPERTY('InstanceName')

* Noel

Jim Zippeltoff Valued Member Points: 53 More actions · Answer 6

Thanks, but the answer is not this easy

This will result to null, if you have a default instance, so it won't be that unique

Server name either, because servers with same names can easily exist with the namepolicies companies usually have to my knowledge.