Windows Server Known / SQL Server Known / SQL Instance UNKNOWN

Question

Post reply

Windows Server Known / SQL Server Known / SQL Instance UNKNOWN

defyant_2004

SSC Eights!

Points: 921
More actions
January 6, 2014 at 12:15 pm

#288090

Hello,
We were given a list of SQL Servers in our environment which included the Windows Server name. When trying to connect via SSMS from a workstation, some of the SQL Servers will NOT connect. I suspect that is because it is not using the default MSSQL server instance name.
If given a machine name and but NOT given the ability to RDP to the machine, and it known that SQL Server is installed, can the instance name be identified?
Thanks!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

You could query the remote registry for the instance names:

reg query "\\SERVERNAME\HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"

If you need to find the ports the services are listening on...

reg query "\\SERVERNAME\HKLM\SOFTWARE\Microsoft\Microsoft SQL Server" /f "TCP*Port" /s

I am sure there will be PowerShell methods too...but I don't know them 😉

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

defyant_2004 SSC Eights! Points: 921 More actions · Answer 2

It looks like I will need to ask the Server Admin to hop on the machine and check the Services to let me know what instances are on each machine. Unfortunately, he just gave me the server name with no RDP access.

The query remote registry did help with 1 server.. thanks. Unfortunately, the other servers I attempted gave me an access denied message.

If anyone knows another trick to identify the instances on a machine that has SQL installed, it would be appreciated. Our Server Admin seems to think all I need is the server name and a local installation of SSMS.

defyant_2004 SSC Eights! Points: 921 More actions · Answer 3

Let me ask this: If your given a list of computer names that have SQL Server installed and asked to take on managing them, wouldn't you at least need to be given the instance name along with it? My server admin seems to think the machine name is all he needs to provide.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

defyant_2004 (1/6/2014)
Let me ask this: If your given a list of computer names that have SQL Server installed and asked to take on managing them, wouldn't you at least need to be given the instance name along with it? My server admin seems to think the machine name is all he needs to provide.

I would think you need RDP access to the systems, and access to each instance either through a security group membership or the 'sa' account.

Without either of those, you are not going to be able to manage anything on those servers. You probably cannot even connect to SQL server without having some sort of account - and if that account is not sysadmin then you probably can't do anything with them anyways.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

dan-572483 SSCoach Points: 15870 More actions · Answer 5

System administrators often don't know much about SQL and won't know about instance names. This has happened to me occasionally. Here's what I've done:

1) If I can't connect with just the machine name, the first thing I do is see if it's SQL Express. Try connecting to <machinename>\SQLExpress

2) Could be a failover cluster instance. The System administrator might know if the servers are clustered and give you the Resource Name.

3) If you click Connect in SSMS and <browse for more> some named instances you're looking for may eventually come up, but I wouldn't count on it.

4) Could be not configured for network access or the local firewall may be blocking access.

At this point you need the system admin to RDP on and look at the service names or investigate remote access or firewall issues - or if it's even MSSQL! More than once I've been asked to fix a SQL server which turned out to be Oracle or MySQL. But you're "the database guy" and need to figure it out.

dan-572483 SSCoach Points: 15870 More actions · Answer 6

Jeffrey Williams 3188 (1/6/2014)
I would think you need RDP access to the systems, and access to each instance either through a security group membership or the 'sa' account.
Without either of those, you are not going to be able to manage anything on those servers. You probably cannot even connect to SQL server without having some sort of account - and if that account is not sysadmin then you probably can't do anything with them anyways.

Good point. You can tell the difference between not being able to connect to a SQL instance and not having access to one - the error message is different. "Login Failed" means you have the right instance name, just not the right login credential or permissions.

defyant_2004 SSC Eights! Points: 921 More actions · Answer 7

I have been given SysAdmin rights for the all machines with SQL Server installed. I have been able to figure out the SQLExpress instances. I was also able to figure a couple of instances out with a custom name. I have about 10 more servers that have SQL Server installed where I am SysAdmin. The problem is, neither MSSQL or SQLExpress are the instance names. I was only given a machine name and sysadmin rights,, I do not have RDP access and the server admin says he can't help me because he doesn't know anything about instances. I am battling trying to get RDP access so I can explore the machine and figure it out.

In this case, is the only option to argue for RDP access?

dan-572483 SSCoach Points: 15870 More actions · Answer 8

I'd say so. This is like if someone in Seattle asked you to meet at Starbucks but couldn't provide an address or location.

benjamin.reyes SSCertifiable Points: 5249 More actions · Answer 9

Do you have access to powershell?

http://www.powershellmagazine.com/2013/04/24/pstip-enumerate-all-sql-server-instances-in-a-network/[/url]

[Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers($false)

Or you could check the services like so:

get-service -computername NAME

mister.magoo SSC-Forever Points: 47174 More actions · Answer 10

defyant_2004 (1/6/2014)
I have been given SysAdmin rights for the all machines with SQL Server installed. I have been able to figure out the SQLExpress instances. I was also able to figure a couple of instances out with a custom name. I have about 10 more servers that have SQL Server installed where I am SysAdmin. The problem is, neither MSSQL or SQLExpress are the instance names. I was only given a machine name and sysadmin rights,, I do not have RDP access and the server admin says he can't help me because he doesn't know anything about instances. I am battling trying to get RDP access so I can explore the machine and figure it out.
In this case, is the only option to argue for RDP access?

Why don't you ask the server admin to just run that registry query on each machine for you ?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

arnipetursson SSCertifiable Points: 6557 More actions · Answer 11

These instances may still be default instances, but not listening on the default port with SQL browser turned on.

That being said, in order to administer a SQL server instance you need relatively elevated privileges on the server with RDP access.

Ideally you want Admin privileges.

defyant_2004 SSC Eights! Points: 921 More actions · Answer 12

Thanks for all the feedback. I will be presenting my case to the Server Admin for RDP access tomorrow morning.

benjamin.reyes SSCertifiable Points: 5249 More actions · Answer 13

I don't think RDP access is necessary, but it helps. Almost everything you need RDP for you can do with SSMS or Powershell.

dan-572483 SSCoach Points: 15870 More actions · Answer 14

Maybe you can do it with Powershell, but for some tasks doesn't it take more time without removing the risk of breaking something?