0

I have a Microsoft SQL Server, running in a Window Server 2012 R2 Standard. The configuration for this SQL Instance is: Servername: IP\SQLEXPRESS( IP here is the IP address of the Window Server) with SQL authentication username and password.

I would like to connect to this remote database from my local machine, so I installed sqlcmd on my local machine Win 7, and tried the syntax to connect to this remote server: sqlcmd -S IP\SQLEXPRESS -U username -P password, but then I got some error messages:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SQL Server Network Inte rfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. .

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or in stance-specific error has occurred while establishing a connection to SQL Server . Server is not found or not accessible. Check if instance name is correct and i f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

I searched some solutions in the Internet and did some configurations for the SQL Server, such as: enabled remote connection in SQL Server, activate TCP/IP Protocol, open incoming TCP Port 1433 by defining new rule in Windows Firewall of remote server, open outgoing TCP Port 1433 in my local Win 7 machine. But it seems there changes are not work in my case. Can anyone give me some hints here to fix this remote connecting problem?

Thank you in advance!

Improve this question
9
  • Did you restart SQL Server after enabling remote connections? Make sure the SQL Server Browser service is running to resolve the instance name to dynamic port. Named instances like SQLEXPRESS may listen on a different port. Check low-level port connectivity with this PowerShell command: powershell -Command echo ((new-object Net.Sockets.TcpClient).Client.Connect('yourserver', 1433)) "success" Commented Jan 8, 2018 at 13:09
  • @DanGuzman hi Dan, yes I did restart the SQL Server after enabling remote connections and SQL Server Browser service is also running. Only I tried your PowerShell command check from my local machine but it was not successful. I got the error something like the server is not response, MethodInvocationException and SocketException. Do you have any idea about this exception? Commented Jan 8, 2018 at 13:20
  • That means the client cannot reach the port, either because the server isn't listening or the port is blocked. First. check the SQL Server error log to see what interfaces and ports SQL Server is listening on. Commented Jan 8, 2018 at 13:39
  • I did check the SQL Server Error log file, and here what I see: The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/VMPW-QA:SQLEXPRESS ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/VMPW-QA:1433 ] for the SQL Server service. Windows return code: 0xffffffff, state: 63 Commented Jan 8, 2018 at 14:03
  • Do you see "listening" messages, like "SQL Server is listening on [ 'any' ipv4 1433]"? The SPN messages are not related to IP connectivity. Commented Jan 8, 2018 at 22:31

1 Answer 1

Reset to default
1

try use only sqlcmd -S IP not IP\SQLEXPRESS

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.