1

I'm busy setting up a MS SQL Server instance on a Windows Server

The server is Windows Server 2012 R2 and the version of MS SQL Server is 2014 Essentials.

I've installed MS SQL and created my database, I've also installed HeidiSQL to test making connections to the server.

All this software is running off of the same machine.

When I run the MS SQL Management studio it connects to

Server name    : SPKWINVMSERVER\SQLSERV
Authentication : Windows Authentication

So I've tried to create a login using the management studio

right click Security > New > Login

and I've created the login as

Login name : mssqladmin
password   : #FongKong123

I gave this user all the server roles for testing and it seemed to create ok.

However when I try to connect to it using this Login I've just created using HeidiSQL it fails

The connection details I'm using in HeidiSQL to connect are

Network type : Microsoft SQL Server (TCP/IP)
Hostname     : SPKWINVMSERVER\SQLSERV
User         : mssqladmin
Password     : #FongKong123
Port         : 1433

The above connection seems to work when connecting to the SQL Server on the server itself.

If trying to connect with another computer on the network (also using HeidiSQL) I imagine I won't be able to use SPKWINVMSERVER\SQLSERV as the hostname but it doesn't work on IP address which is in this case 192.168.20.67

Network type : Microsoft SQL Server (TCP/IP)
Hostname     : 192.168.20.67\SQLSERV
User         : mssqladmin
Password     : #FongKong123
Port         : 1433

What did I miss?

Improve this question
7
  • 1
    Do you have SqlServer authentication enabled? Go to the instance->properties->security to check it
    – vercelli
    Aug 22, 2016 at 11:24
  • The server authentication is set to SQL Server and Windows Authentication mode
    – TheLovelySausage
    Aug 22, 2016 at 11:27
  • I've updated to include instance name (PS they are both running off the same machine)
    – TheLovelySausage
    Aug 22, 2016 at 11:29
  • 1
    Go to HeidiSQL -> security and look for the new login. Maybe you didn't map it to the DB.
    – vercelli
    Aug 22, 2016 at 11:34
  • Ok so the only other change I made was to change 127.0.0.1\SQLSERV to SPKWINVMSERVER\SQLSERV and that made it work but that is a problem in itself because how would I do it from a remote computer using an IP address
    – TheLovelySausage
    Aug 22, 2016 at 11:44

2 Answers 2

Reset to default
1

Go to Sql Server Configuration Manager, and check on your client protocols if TCP/IP is enabled.

Improve this answer
3
  • TCP/IP is enabled in the Server Configuration Manager
    – TheLovelySausage
    Aug 22, 2016 at 12:04
  • @Trent Firewall? is telnet working?
    – vercelli
    Aug 22, 2016 at 12:20
  • The firewall is disabled atm, I tried to telnet 192.168.20.67:1433 and it failed with a could not open connection error
    – TheLovelySausage
    Aug 22, 2016 at 12:25
1

I believe I've found the reason this wasn't working, it seems none of the traffic was targeting port 1433.

To fix it I had to run the SQL Server Configuration Manager and then do the following

Navigate to TCP/IP under SQL Server Network Configuration > Protocols for SQLSERV

Right click TCP/IP and select Properties

Under the IP Addresses tab I had to add 1433 to TCP Port for all of the available IP Protocols

seems to be working ok now, thank you to everyone though for helping me get that far though

Improve this answer
1
  • Thanks for updating. If you can mark this as the answer, that would definitely help everyone out in the future. I've run into this very problem/solution a few times over the years.
    – SQLDevDBA
    Aug 22, 2016 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.