How to connect SQL Server by ConnectionString stored in Web.config?

Question

I am trying to use a windows server with IIS and SQL Server to host a website. However, with simple SQL Server ODBC-connection I unfortunately found that there is no chance to work in IIS. After looking for a solution I found that I somehow need to connect SQL Server to IIS. Everybody is taking about a connection string which should be put into "the web.config" file.

String:

<connectionStrings>
    <add name="MyProjectContext" connectionString="Server=.\SQLEXPRESS;Database=MyProject;User Id=John;Password=duck;" providerName="System.Data.SqlClient" />
</connectionStrings>

But where exactly do I need to put this, where is this web.config file, how can I get there?

Does this answer your question? [ASP.NET: How to create a connection from a web.config ConnectionString?](https://stackoverflow.com/questions/1046042/asp-net-how-to-create-a-connection-from-a-web-config-connectionstring) — Filburt, Oct 28 '21 at 18:54
What language / platform are you developing your web app in? The web.config file is for various flavors of asp.net. If you are not using that it's likely that there is a different solution that you're looking for. — squillman, Oct 28 '21 at 19:07
I am using PHP as backend. Is there something else if you’re not using .NET? — alexcc, Oct 29 '21 at 07:24

score 0 · Answer 1 · answered Oct 29 '21 at 15:08

thanks for your help. After researching and trying a lot of things I found a great solution on how to connect my database SQL Server in IIS with PHP.

You need to create a Web.config file in your website path.
Inside this Web.config file paste the connection string e.g.

<?xml version="1.0"?>
<configuration>
<connectionStrings>
    <add name="sqlDB" connectionString="Server=xxx.xxx.xxx.xxx; Database=db_name; User Id=sqlserver_user_name; password=sqlserver_password;Max Pool Size=2000;" providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>

Then create file e.g. sql.php in same path for instance (the example below doesn't need to be changed)

<?php
    // start get connection string from web.config
    $xml =  simplexml_load_file("Web.config") or die("Error: Cannot create object"); 
    //in my case web.config is in previous directory
    $array = json_decode(json_encode((array) $xml), 1);

    $server = "";
    $database = "";
    $user = "";
    $password = "";

    $fullString = ($xml->connectionStrings->add[0]["connectionString"]);

    $strArr = explode(";", $fullString);

    $arrCount = count($strArr);
    $connArr[]="";
    for($x=0;$x<$arrCount-1;$x++){
        $aS = explode("=",$strArr[$x]);
        $connArr+=array(strtolower(trim($aS[0])) => trim($aS[1]));
    }
    $server = $connArr["server"];
    $database = $connArr["database"];
    $user = $connArr["user id"];
    $password = $connArr["password"];
    // end get connection string from web.config

    $connectionInfo = array( "UID"=>$user,                            
                             "PWD"=>$password,                            
                             "Database"=>$database,
                             "CharacterSet" => "UTF-8"); 

    /* Connect using SQL Server Authentication. */  
    $conn = sqlsrv_connect( $server, $connectionInfo); 
?>

$conn to perform sql_query()

How to connect SQL Server by ConnectionString stored in Web.config?

1 Answers1