0

I'm attempting to remotely connect to my SQL Express 2016 server through MS SQL on my local machine however am having extreme difficulties with the authentication.. Well I assume it is an authentication issue as when I attempt connect to connect to it I receive the error message:

The remote computer refused the network connection.

When connecting through the sqlcmd I am using Windows authentication and have no issue, however to connect to it remotely I need to use SQL authentication and have no memory of creating an account so am wondering if there's a default login and password?

I believe this is the issue however it is quite likely i'm doing something stupid so any assistance would be appreciated!

jarlh
  • 42,561
  • 8
  • 45
  • 63
David Harlow
  • 2,673
  • 2
  • 15
  • 18

1 Answers1

0

You can create an account that can be used to connect to that SQLExpress instance by using SQL Server Management Studio. Expand the database you wish to create a login for in the Object Explorer. Under the Security folder, you can create a new login by right clicking the Logins folder.

Also make sure that your machine has its firewall configured to allow remote connections, the server has remote connections enabled, and that you are allowing logins for Windows Auth and SQL Server Auth.

You can allow remote connections for a server by opening the SQL Server Configuration Manager. Under the SQL Server Network Confiuration Node, Select Protocols for MYSERVERNAME. Ensure that TCP/IP is enabled.

Here's a guide on setting up the firewall: https://technet.microsoft.com/en-us/library/ms175043(v=sql.110).aspx

You can enable SQL Server Authentication by right clicking your server name in the object explorer in SQL Server Management Studio and going to the Security tab. look for the "Server Authentication" option.

EMUEVIL
  • 502
  • 3
  • 14
  • Should had mentioned.. my SQL Express server is on a server without MS SQL due to server space, hence why I want to remotely connect to it from my local machine with MS SQL. And how would I allow logins from Windows and SQL Server Auth? Without MS SQL? – David Harlow Apr 17 '17 at 18:53
  • @DavidHarlow when you say that you don't have MS SQL? do you mean that you don't have the SQL Server Management Studio installed? You can always create a login with a query, but you'll need to run it from an existing login. Look at the options provided in this link for (Transact-SQL) not SSMS: https://msdn.microsoft.com/en-us/library/aa337562(v=sql.105).aspx – EMUEVIL Apr 17 '17 at 18:58
  • @DavidHarlow I'm not an expert on SQL Server configuration. Perhaps try to find a way to allow remote login using Windows Authentication. I'm sure someone else that has done more could tell you what is possible. I've never had to make a login without using SSMS. – EMUEVIL Apr 17 '17 at 19:04
  • Yes I don't have SQL Server Management Studio.. Thanks for that, just created a login and have attempted to login with it with no success :( my firewalls and ports are all set up correctly to my knowledge, any other suggestions? – David Harlow Apr 17 '17 at 19:53
  • @DavidHarlow Make sure that you enable the login. By default, new logins are disabled for security reasons. – EMUEVIL Apr 17 '17 at 20:44
  • @DavidHarlow EDIT: I think i lied to you. I just tested this. I believe it's only certain admin accounts that are created disabled. I think that you should set up a test SQL server in an environment that you can mess around with and try some testing. Sorry I can't be more helpful! – EMUEVIL Apr 17 '17 at 20:50
  • @EVUEVIL Thanks for your help but managed to crack it finally! Was an issue with not having mixed-mode authentication working which was proving an issue without an SSMS, but this answer helped me out in the end http://stackoverflow.com/questions/1393654/how-can-i-change-from-sql-server-windows-mode-to-mixed-mode-sql-server-2008/27381237#27381237 – David Harlow Apr 19 '17 at 12:38