locked
How to connect to SQL server through machine name from asp.net application WEB.CONFIG? RRS feed

  • Question

  • User-96029760 posted

    My live ASP.NET webforms application have to connect to the respective local user's SQL server by using machine name/SQL Express

    Can anyone help out how to configure the datasource connection string in web.config???

    Thursday, August 3, 2017 2:44 PM

Answers

All replies

  • User-1509636757 posted

    live ASP.NET webforms application have to connect to the respective local user's SQL server

    Are both machines (the machine where web application is hosted and local user's machine where SQL server is installed) in same network? This can only be possible if web server machine has accessibility on port 1433 to machine where SQL Server is installed.

    If this is only related to connection string issue; then you can check SQL Server connection strings - ConnectionStrings.com

    For SQL Authentication; usually it would be: data source=machinename\SQLEXPRESS;initial catalog=databasename;user id=sqluser;pwd=sqluserpassword

    In above underlined connection string:

    1. data source: is the instance name of SQL Server Express on machine you want to connect
    2. initial catalog: is the name of database
    3. user id: is the SQL Server user name that you will require (for example, sa)
    4. pwd: is the SQL Server user password that you will require to connect
    Thursday, August 3, 2017 2:57 PM
  • User-96029760 posted

    Hi Kaushalparik,

    Yes. both are in same network.

    All of the users are having SQL installed in their machines. When we run the asp.net web application, it has to connect to local SQL Server of respective user who opened the application.

    Can machine name be passed dynamically to connection string?

    =========================================================================

    Regards,

    Praveen

    Thursday, August 3, 2017 3:58 PM
  • User753101303 posted

    Humm it is very unusual.

    As a first step try to see if https://msdn.microsoft.com/en-us/library/system.web.httprequest.userhostname(v=vs.110).aspx returns the correct host name or IP address.

    Also by default SQL Express is not configured to accept external connection. See http://support.webecs.com/kb/a868/how-do-i-configure-sql-server-express-to-allow-remote-tcp-ip-connections-on-port-1433.aspx

    Also your app will need to use an account that is allowed to connect to those local SQL Server serveurs...

    You'll likely need to use a place holder in your connection string (Data Source={0} for example) and then use String.Format to use the correct machine name when an http request is processed).

    Edit: this is basic idea but the SQL Server machine you want to reach dépends on the machine from which the browser is used or from the connected user (ie what if someone uses another machine than its usual one ?).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 3, 2017 4:15 PM
  • User-96029760 posted

    Hi PatriceSc,

    Each user opens SQL Server in their own machine only. 

    Thursday, August 3, 2017 4:32 PM
  • User753101303 posted

    Ok so I would start with a test page that shows  https://msdn.microsoft.com/en-us/library/system.web.httprequest.userhostname(v=vs.110).aspx to see if I get the correct machine name or IP.

    If yes, use this value in your connection string using a format string such as Data Source={0}\SQLEXPRESS etc... (assuming all machines have a SQLEXPRESS instance).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 3, 2017 4:41 PM
  • User1120430333 posted

    Hi Kaushalparik,

    Yes. both are in same network.

    All of the users are having SQL installed in their machines. When we run the asp.net web application, it has to connect to local SQL Server of respective user who opened the application.

    Can machine name be passed dynamically to connection string?

    =========================================================================

    Regards,

    Praveen

    What? You are deploying MS SQL Sever Express on every user's machine? What kind of application is this? Is this is an ASP.NET Web form application using local IIS and using MS SQL Server local to the machine?

    Thursday, August 3, 2017 6:06 PM
  • User-96029760 posted

    Hi PatriceSc,

    It worked for me. Thank you so much  Smile

    Friday, August 4, 2017 3:11 AM