Could not open a connection to SQL Server | ASP.NET | C# | MVC

Allow Remote Connections SQL Server 2012

Connection / February 1, 2018

Sp_configure 'remote access', 1 doesn't do what you think it does. The option name is misleading as it has nothing to do with allowing/disallowing users to access the server from a remote location, it's server-to-server connections. There's a ton of misinformation out there on this. Try it yourself, set the value to 0 then connect to the SQL Server from a different machine. So long as a network protocol is enabled and there are no firewall blocks, you will connect successfully.

As for your users, unless your users typically connect to the machine/vm where your SQL Server instance is hosted to access the SQL Server, all are remote users and that should be way. Unless your SQL Server is running on a network that is publicly accessible, there's little harm/risk in letting the server accept all incoming requests. These requests have to be authenticated and authorized anyway so it's not like you're letting random connections through.

If you really want to lock it down so only specific users can even attempt to connect to SQL Server, you can do it via the Windows firewall with Advanced Security Settings. You've probably already configured an inbound rule to allow connections from other machines so just modify that rule by restricting access to specific users only. Just look for the Users tab and add the authorized users from there (you'll need to require secure connections only in the General tab). If you haven't yet configured an inbound rule, just create a new one and add the restrictions on users mentioned above.

Again, if the SQL Server is running in a corporate network not directly accessible by outsiders/the internet, this type of lockdown is rarely needed. If you really have some highly sensitive data in there then this lockdown is probably insufficient.