Cannot Connect to Local Server Group

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 Administration

Cannot Connect to Local Server Group

Author

Topic

calisonic
Starting Member

32 Posts

Posted - 2015-02-16 : 19:14:45

I am really new to this so please bare with me..
I have an old Dell PE 2950 refurb at my house that I bought for training purposes. It's running Windows Server 2012 R2. From there I installed a hyper v instance of 2008 R2 Datacenter.

Following my instructional video I installed an SQL instance "sql2008" on the Server 2008 R2 OS with an SA account "sqladmin" using SQL Server Authentication with most of the features selected. I also added my regular Windows admin account during the install "Admin" as an SA account.

Issue is when I launch SSMS, go to Registered Servers>Database Engine>Local Server Groups> and right click my instance > Object Explorer I get an error "Error connecting to sql2008" lisitng a network related or instance specific error occurred while establishing a connection to SQL Server. "The server was not found or was not accessible". Also said to verify that "SQL server is configured to allow remote connections.".

I wouldn't think remote connection configuration is needed as this is all running locally, but nonetheless I googled and did virtually everything I could including manually ensuring TCP was all enabled and manually setting SQL Configuration Manager to use port 1433 under the IPALL section, (which I found info on much easier after I realized it wasn't IPAL). Also tried everything here: http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx I also imagine permissions wouldnt bne an issue as I made sure both the SA accounts were in ALL of the SQL related groups on in Local Users and Groups.

I also disabled all firewalls/antivirus and updated the Server 2008 OS (the later of which took forever).

It should also be noted that in SSMS, under Registered Servers > Local Server Groups, my instance has a blank white dot on it instead of the green dot w/ white arrow that indicates "running" like my instructional video shows.

I betting it must be something relate to permissions but cannot for the life of me figure out what.

Any ideas?
Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-16 : 19:42:30

Check Services in Control Panel/Admin Tools for the services that start with SQL Server. Are they in a started state?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

calisonic
Starting Member

32 Posts

Posted - 2015-02-16 : 19:58:35

Ah yes, forgot to mention I started those as well.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-16 : 20:02:43

Did you restart the services after you reset the port to 1433? Verify the port it's listening on by viewing the error log file. On my local system where I installed SQL 2012 using the default paths, my error log is located here: C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Log. Yours will vary. Open up the ERRORLOG file (no extension) in notepad or similar. You are looking for a line similar to this: Server is listening on [ 127.0.0.1 <ipv4> 3919]. What port does it show?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

calisonic
Starting Member

32 Posts

Posted - 2015-02-16 : 20:13:54

For ipv4 and 6 Server is listening on 49442 and on another line, 49443

calisonic
Starting Member

32 Posts

Posted - 2015-02-16 : 20:16:08

quote:
Originally posted by calisonic

For ipv4 and 6 Server is listening on 49442 and on another line, 49443

I should mention I set the ipALL configuration in sql configuration manager back to the defaults with tcp dynamic port 49442 and a clear value on TCP port.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-16 : 20:20:24

Let's try connecting with the port number then as that often can get around that error. Try connecting with Hostname,49442 and also Hostname,49443. Replace "Hostname" with your actual host name. This is not the instance name, but rather the computer's name. You can find that info by typing in hostname in a cmd window, or you can reference it in the ERRORLOG file. It'll say "Server name is 'Hostname\Instancename'. You only care about the part before the slash for this connection test.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

calisonic
Starting Member

32 Posts

Posted - 2015-02-16 : 20:21:33

quote:
Originally posted by calisonic

quote:
Originally posted by calisonic

For ipv4 and 6 Server is listening on 49442 and on another line, 49443

I should mention I set the ipALL configuration in sql configuration manager back to the defaults with tcp dynamic port 49442 and a clear value on TCP port.

In addition, after setting it BACK to 1433 AND clearing the dynamic field AND restarting services, the errorlog shows listening on port 1433, however I still cannot connect to my instance in SSMS.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-16 : 20:23:09

If it is now listening on port 1433, then do the connection test with that port too: Hostname,1433.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

calisonic
Starting Member

32 Posts

Posted - 2015-02-16 : 20:29:44

Sorry, where exactly am I typing in the hostname (HYPERVWS2008R2)and port to connect ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-16 : 20:32:00

In Management Studio's Object Explorer, click Connect, then Database Engine. Type in your hostname and then a comma and then try each of the 3 ports shown in the ERRORLOG.

HYPERVWS2008R2,49442
HYPERVWS2008R2,49443
HYPERVWS2008R2,1433

Do any of these connect successfully?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

calisonic
Starting Member

32 Posts

Posted - 2015-02-16 : 20:37:55

When I try 49443 or 49442 I get the same error as I initially oposted about. If I try 1433 I get Log in failed for user 'sqladmin' Error 18456. Same thing if I try my Windows Admin account.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-16 : 20:39:19

The login failed one means you did connect, but SQL didn't allow you in due to a bad login name or password.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-16 : 20:40:25

You could try the steps here to regain access: http://www.mssqltips.com/sqlservertip/2465/how-to-connect-to-sql-server-if-you-are-completely-locked-out/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

calisonic
Starting Member

32 Posts

Posted - 2015-02-16 : 20:41:41

Yes I see that via google.. very odd as these u/n's are accurate and the password is one I have used for years. Multiple attempts yield same result.

I should def be using the SA account credentials I set up during SQL installation, correct ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-16 : 20:45:22

Windows authentication is recommended. sa should basically never be used. It's recommended to get rid of that account actually, but let's not do that to your system.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

calisonic
Starting Member

32 Posts

Posted - 2015-02-16 : 20:45:51

Also, I am not seeing the directory in my C: (only) drive listed in the link you just provided..

calisonic
Starting Member

32 Posts

Posted - 2015-02-16 : 20:46:21

quote:
Originally posted by calisonic

Also, I am not seeing the directory in my C: (only) drive listed in the link you just provided..

Or any .binn file to be exact.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-16 : 20:57:14

Your path will likely be different. BINN is a folder though. My BINN location for my default instance of SQL 2008 R2 is this: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

If yours is a named instance, it'll be different. The path is configurable when you install it, and the person who wrote that article changed the default path.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

calisonic
Starting Member

32 Posts

Posted - 2015-02-16 : 20:57:49

Ok, just kidding, found the file, however when I attempt to run the 'sqlservr -m' command I get a blank error box. Tried with SQL services running and not running.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-16 : 20:59:22

SQL must be stopped for that article. You are starting it from the command line. As noted in the article, it'll take a bit to startup.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources