locked
Database Connection Problem at Local Isolated Network RRS feed

  • Question

  • Hi guys,<o:p></o:p>

    I am using the SQL Server 2017 Express Edition and can not establish the link between the other local computers with my database at my hard disc in my ado.net application. My transactions are as below:

    1- The connection link is "Server=ip_address,1433\SQLEXPRESS;Database=DatabaseName;User=sa;Password=psw"<o:p></o:p>

    2- Configuration Manager Native Client and Network Configurations TCP/IP Enabled and Active=Yes and set the Port Numbers=1433<o:p></o:p>

    3- SQL Server Services ->restart<o:p></o:p>

    4- Windows Firewall -> Advanced Settings -> New Inbound Rule -> Rule Type=Port and Port Number=1433, Allow All Connections<o:p></o:p>

    Altough I performed all of these procedures, the connection can not established. <o:p></o:p>

    I would like to thank you for your assistance in advance.<o:p></o:p>


    Thursday, January 25, 2018 8:40 AM

Answers

  • I solved the problem by editing the connection string and configuration manager values as below: 

    "Server=ip address\SQLEXPRESS,1433; Database=dbname; User=sa; Password=psw".

    "IP All (dynamic)=1433"

    Thanks for your assistance.


    • Marked as answer by Yusuf C Thursday, March 1, 2018 8:05 AM
    Thursday, March 1, 2018 8:04 AM

All replies

  • "Server=ip_address,1433\SQLEXPRESS;Database=DatabaseName;User=sa;Password=psw

    Hello,

    You Connection string is a bit wrong, either use the IP port or the instance Name, but not both=>

    Server=ip_address,1433;Database=DatabaseName;User=sa;Password=psw;

    Is the SQL Server running in "Mixed Mode" = Windows + SQL Authentication? Default is "Windows only", you use SQL Auth => Change Server Authentication Mode

    Note: Never use the SysAdmin logon "sa" for application, create an SQL Login with only the required informations.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Teige Gao Monday, January 29, 2018 2:42 AM
    Thursday, January 25, 2018 9:07 AM
  • Hi Olaf Helper,

    I am using mixed mode authentication and if i manage to connect local computers to my database, i will organize the user profiles. Thanks for your advice.

    My connection string is working in my computer as below but other local computers:

    Server=ip_address\SQLEXPRESS;Database=DatabaseName;User=sa;Password=psw;

    But when I added port number with comma (Server=ip_address\SQLEXPRESS,1433) it does not work at all. I guess the problem at firewall settings or another port issue. But i think that I did all necessary steps. Completely confused.



    • Edited by Yusuf C Thursday, January 25, 2018 11:18 AM
    Thursday, January 25, 2018 9:56 AM
  • Hi Yusuf C,

    The connection string "Server=ip_address\SQLEXPRESS,1433" is not a valid one, we can only use IP +"," + port number or server name\instance name to connect to it. For example 1**.1**.1.1,1433 or server name\instance name, for more information, please refer to this article: https://www.connectionstrings.com/sql-server/

    When it is 1433 port, we can use the IP address connecting directly. 

    >>Windows Firewall -> Advanced Settings -> New Inbound Rule -> Rule Type=Port and Port Number=1433, Allow All Connections

    You have configured the firewall.

    Best Regards,

    Teige


    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 Teige Gao Monday, January 29, 2018 2:51 AM
    Monday, January 29, 2018 2:50 AM
  • 4- Windows Firewall -> Advanced Settings -> New Inbound Rule -> Rule Type=Port


    It's a bi-directional communication, you have to add outbound rule, too, and this on server and client side, see Configure the Windows Firewall to Allow SQL Server Access. Additional, port 1433 is the default for a standard instance, you use a named instance (SQLEXPRESS), which may use a different IP port; see link for how to get the used port.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Monday, January 29, 2018 5:39 AM
  • I solved the problem by editing the connection string and configuration manager values as below: 

    "Server=ip address\SQLEXPRESS,1433; Database=dbname; User=sa; Password=psw".

    "IP All (dynamic)=1433"

    Thanks for your assistance.


    • Marked as answer by Yusuf C Thursday, March 1, 2018 8:05 AM
    Thursday, March 1, 2018 8:04 AM