none
Setting up my instance of SQL server to allow remote connections RRS feed

  • Question

  • So this question "can" become convoluted because people always question the why of what I'm doing and it "will" get difficult because I forgot which instance is currently running either 2012 or later that I set up ages ago and that I don't know why the wizard can't find the installation media I searched the usual places I keep such things. So before I wipe the machine an go through very complex issues resetting up a whole IDE environment just to work on a solution and it's layers of application tiers to demonstrate a model that I already know myself works and have demonstrated through other implementations I have a related chain of information to ask the question...

    ...How to I enable remote connections for my SQL instance? What tools for example Server Management Studio, Instance Wizard... and or how do I check what version of the instance I have and or update that instance to allow me to publish an application needing remote connections to it. 

    What I've Tried so far;
    My MS SQL instance that the app I'm publishing from visual studio 2012 detects that as well as it exists in my Server management studio that allows me to view that local host. I need to publish to that instance but the publisher said that I have to enable remote connections. My though was trying the wizard to either repair or replace or add and new instance but any attempts ask for the installation media it's apparently not the same as the media I used to install the wizard that sets up instances because that I know is in one of several folders for versions I've used over the years SQL 2012 up through SQL 2016. Now being as this is a development studio in Visual Studio 2012 and I was looking to match the original properties for solution files it may not be ne newest it might be SQL 2012 express it might be 2012 developer it might be 2016 I don't know. What I do know is that the wizard is not recognizing any of those folders as installation media for creating or repairing this instance.

    So I'm trying to avoid needing to have a lot of complex new setup and having to format my computer just to reset up and hope for the best on a new laptop. Especially when my project seems to want this exact instance for encryption keys, computer machine setup, IDE, tools within the IDE and names user accounts passwords and if I get any of that wrong my solution may not work again. I've tried it on other machines even this one I'm typing on was the original machine but it didn't want to work on here only there.



    Cause reasons (not necessary that you critique my process and or even read this part but your going to ask so);

    I neeeed my connection string from the application solution I publish to match the one I put in for my pivot table. (period)...

    ...so I can continue the experiment I did in a different implementation to prove the model can be flattened for this purpose.

    Basically I just want to get the connection strings straightened out so I can demonstrated using a document to connect to the database with a single user pivot table document that creates useful very complex to create but easy to use administration documents similar to a Form letter but in a more dense tabulated grid the way I did in excel with cells, merged cells, character counts in the database and excel, multiple tabs for black and white/color/repeating sheets/cover sheets/macros/VBA loop and count code/Print boundaries and printer settings/size/conditional formatting for the final print product. This was started first through some post processing from the flattened pivot tables to do things with the non normalized data that the application tier in the solution before reaching excel that could not be done in advance and then feed back out of those hidden sheets formatting and arranging the info further for print sheets that. It's complicated to say the least but basically lots of stuff happens behind the scene to arrange data in a process that doesn't change so long as I have the data come in from the data base via the connection sting I'm looking for with a pivot table that flattens so normalized and some no normalized data for the slicers and pivot table to make rows and then move that data into the processing on to the print tables I'd setup.

    The solution is a tier based project it has a lot of things one of those is an application tier for data entry into the database and forms and form controls data bound to it, one piece is SQL database meant for microsoft SQL servers to store data. My reasons are I need to publish the database structure with some placeholder data into the eco system. Instead of needing an updating app this is a document very complex cube logic document that will provide the necessary print media used in the field. It will be connecting to the data base and getting the most up to date info and allows so that from that anniversary time once a month one a day etc... a place for extra or new info to be placed there until just before the new anniversary it's entered into the application data entry tier for the data base before this cycle repeats and the pivot table data base is updated and printed for the next month, day, or year.

    Monday, May 13, 2019 6:34 PM

All replies

  • Hi,

    >>How to I enable remote connections for my SQL instance? What tools for example Server Management Studio, Instance Wizard

    You can simply use SQL Server Management Studio. In SSMS, right click on the server and select the Properties option. In the Server Properties dialog under the Connections tab check the Allow remote connections to this server option:

    >>how do I check what version of the instance I have

    You can run select @@version in your instance. 

    >>update that instance to allow me to publish an application needing remote connections to it. 

    Please check the following items:

    1. Make sure SQL Server Service is running and you have enabled TCP/IP protocols
    2. If a named instance, make sure SQL Server browser service is running
    3. Make sure SQL Server is configured to allow remote connections
    4. Examine the SQL Server error log for messages confirming that SQL is listening on the expected network interfaces and ports
    5. Test server connectivity with PING from the client machine
    6. Test port connectivity using TELNET or PowerShell to the server and port (from step 4) from the client machine.  For example
    a. TELNET <server-name> 1433
    b. PowerShell: 1433 | % { echo ((new-object Net.Sockets.TcpClient).Connect("YourServerName",$_)) "server listening on TCP port $_" }
    7. Check firewall settings if step 5 or 6 connectivity test fails

    For more information, please refer to  Connecting from Another Computer


    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    • Edited by Puzzle_Chen Tuesday, May 14, 2019 3:00 AM
    • Proposed as answer by pituachMVP Tuesday, May 14, 2019 5:45 AM
    Tuesday, May 14, 2019 2:57 AM
  • I doubt that the settings recommended above will help you. It controls where you can execute stored procedures *from another SQL Server* on this server - a feature that was replaced by linked servers in 7.0. See here: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-access-server-configuration-option?view=sql-server-2017.

    To allow others to connect to a SQL Server you need to make sure that it listens to TCP/IP Netlib (aka protocol), using SQL server Configuration Manager (or script it using its WMI provider). You can of course also enable the Named Pipes netlib, but that is ancient. Non of those netlibs are enabled by default in Express or Developer Edition.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, May 14, 2019 10:47 AM
    Moderator
  • It's just me on the same local host machine at the moment.

    Here is what I just tried.

    Ok so found these instructions
    https://blogs.msdn.microsoft.com/walzenbach/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008/

    First in SQL Management Studio
    -I checked in SQL manager that remote connection were found to be already allowed.

    Second Ran SQL Configuration manager
    -first off the manager that is bundled with the SQL 2016 folder in the start menu would not run
    -Second the manager in the start folder for 2012 would run this leads me to believe it's SQL 2012 confirmed with SELECT @@VERSION Query = 
    Microsoft SQL Server 2012 11.0.2100.60 (X64)
    -Third TCP/IP is already enabled so moving on

    Third in Firewall Management
    I set up a new rule in my Security software to add a rule to the firewall for outbound/inbound TCP and tried opening both Remote and Local ports for 1433 the SQL Database port. Unfortunately my application both during publishing and after publishing still has the same errors still not communicating of remote ports or with the installed apps database even with this firewall rule and everything else ruled out.


    I haven't ping the pinged or checked that it's listening on the network I doubt it is not it's default settings but if I get instructions on how to doe these two things I will. I think the issue is in somehow when the app is publishing it's setting up remote communication to the apps database and I'm working with old code on something I'm still prototyping with a code base set to be unsupported now and no more after this year. I'm going to try cleaning and rebuilding the project and solution code at those levels give it another try.
    • Edited by reigh7 Wednesday, May 15, 2019 5:59 AM
    Wednesday, May 15, 2019 5:40 AM
  • Use configuration Manager in your Computer Management and see if that work. Don't guess what version of SQL Server you have. Connect to it and check. My guess is that it is of a higher version and you didn't check the right instance, and the instance in question do not listen to the TCP netlib.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, May 15, 2019 2:00 PM
    Moderator
  • I queried the using Select @@Version as Puzzle_Chen suggested it is 2012 t be precise it's Microsoft SQL Server 2012 11.0.2100.60 (X64)  makes sense for me because I was trying to eliminate variables for a very old project using a legacy version of visual studio.

    Also I tried to describe I used configuration manager for 2012 and it showed that TCP/IP was already enabled and that's when I looked at the firewall rules and added on for remote connections for that port.

    I am also now sure of the connection string being used in my VS project if that's helpful at all it's connecting the named instance of the local machine it's one and some variables same name as the one showing in SQL manager although it's not publishing to it and I tried to connect to while running in the debugger using that connection string from the config file. for prototyping but no go either.

    There were some suggestion I don't know hw to do exactly from  Puzzle_Chen
    4. Examine the SQL Server error log for messages confirming that SQL is listening on the expected network interfaces and ports
    5. Test server connectivity with PING from the client machine
    6. Test port connectivity using TELNET or PowerShell to the server and port (from step 4) from the client machine.  For example
    a. TELNET <server-name> 1433
    b. PowerShell: 1433 | % { echo ((new-object Net.Sockets.TcpClient).Connect("YourServerName",$_)) "server listening on TCP port $_" }

    Wednesday, May 15, 2019 8:29 PM
  • I queried the using Select @@Version as Puzzle_Chen suggested it is 2012 t be precise it's Microsoft SQL Server 2012 11.0.2100.60 (X64)  makes sense for me because I was trying to eliminate variables for a very old project using a legacy version of visual studio.

    Also I tried to describe I used configuration manager for 2012 and it showed that TCP/IP was already enabled and that's when I looked at the firewall rules and added on for remote connections for that port.

    I am also now sure of the connection string being used in my VS project if that's helpful at all it's connecting the named instance of the local machine it's one and some variables same name as the one showing in SQL manager although it's not publishing to it and I tried to connect to while running in the debugger using that connection string from the config file. for prototyping but no go either.

    Wednesday, May 15, 2019 8:29 PM
  • Hi,

    >>4. Examine the SQL Server error log for messages confirming that SQL is listening on the expected network interfaces and ports

    You can run exec sp_readerrorlog to check the SQL Server error log. The message would be like 
    2019-05-17 18:14:55.280 Server       Dedicated admin connection support was established for listening remotely on port 1434.

    >>5. Test server connectivity with PING from the client machine

    In the command prompt window, type ping <ip address> and then the IP address of the computer that is running SQL Server.

    >>6. Test port connectivity using TELNET or PowerShell to the server and port (from step 4) from the client machine. 

    Start a command prompt / PowerShell prompt and then enter telnet <erver-name> <port>. Once you hit enter, if the screen goes blank, you've verified connectivity.

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 20, 2019 5:28 AM
  • I'm being told that by PowerShell that telnet is not a cmdlet.

    I was able to get a log there wasn't anything specific about listening  for any specific ports

    as for the ping The instance of visual studio and the server are running on the same development machine. I can get visual studio to run the developing software in the studio but run into those issues when publishing or setting up the connection string for publishing so I can then connect the document piece to connect to the same database info.

    At this point I'd accept being able to connect the documents to the local host within Visual Studio 2012 so I can work on the documents connection? If anyone knows how I can set up a connection string to target that?
    Monday, May 20, 2019 9:49 PM
  • So what I mean by pinging the server is I don't knw the IP and it would be the IP for the dev machine I'm on.

    Other then that and Telnet command let not working in PowerShell?

    • Edited by reigh7 Thursday, May 23, 2019 6:28 AM
    Thursday, May 23, 2019 12:21 AM
  • I was able to ping the server by replacing the ip with the name of the server instance I'm targeting. I sent and received 4 times

    Secondly I figured out how to enable Telnet and run the command in PowerShell with admin privileges. Port 1433 will not allow for connections. I attempted Telnet a second time after making a second rule to allow this port to connect. The first using the security suite I use and the second time using Windows Firewall Tools.

    Friday, May 24, 2019 9:54 AM