{"id":45341,"date":"2016-02-04T22:09:30","date_gmt":"2016-02-04T19:09:30","guid":{"rendered":"https:\/\/www.altoros.com\/blog\/?p=45341"},"modified":"2019-07-15T14:17:45","modified_gmt":"2019-07-15T11:17:45","slug":"net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app","status":"publish","type":"post","link":"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/","title":{"rendered":".NET on Pivotal Cloud Foundry: Adding a MS SQL Service to an App"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_79_2 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#Deploying_a_service_broker\" >Deploying a service broker<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#Creating_a_service\" >Creating a service<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#Modifying_the_NET_app\" >Modifying the .NET app<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#Publishing_the_NET_app\" >Publishing the .NET app<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#Binding_the_MS_SQL_service_to_the_NET_app\" >Binding the MS SQL service to the .NET app<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#Getting_credentials\" >Getting credentials<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#Connecting_the_app_to_the_MS_SQL_service\" >Connecting the app to the MS SQL service<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#Further_reaading\" >Further reaading<\/a><\/li><\/ul><\/nav><\/div>\n<h3><span class=\"ez-toc-section\" id=\"Deploying_a_service_broker\"><\/span>Deploying a service broker<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><img decoding=\"async\" src=\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/dotnet-on-pivotal-cf.png\" alt=\".NET Applications on Pivotal CF: Binding MySQL\" style=\"margin: 0px 0px 15px 20px\" width=\"190\" class=\"alignright size-medium wp-image-45342\" \/><\/p>\n<p>In the <a href=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-connecting-and-pushing-an-app\/\" target=\"_blank\" rel=\"noopener noreferrer\">previous post<\/a>, we demonstrated how to deploy a .NET app to a Pivotal CF instance. However, the application is only partially operational\u2014it still doesn\u2019t have access to a database. In this tutorial, we will explain how to configure a MS SQL DB service and connect it to a .NET app running on PCF.<\/p>\n<p>We will be using a private PaaS deployment, but the same commands will work for Pivotal Web Services (PWS). You can find the full versions of files used in the examples below in <a href=\"https:\/\/github.com\/Altoros\/Altoros-PCF.Net\" target=\"_blank\" rel=\"noopener noreferrer\">this GitHub repo<\/a>.<\/p>\n<p>MS SQL is an external service that can be added to the PaaS via a service broker, which is not available out-of-the-box. Fortunately, it is trivial to install one.  The broker is a Java app, and we can simply push it to the Pivotal CF instance. There are other ways to add it, too. You can find a detailed guide in <a href=\"https:\/\/github.com\/cloudfoundry-attic\/cf-mssql-broker\" target=\"_blank\" rel=\"noopener noreferrer\">this GitHub repository<\/a>.<\/p>\n<p>Once the broker has been installed, we can go ahead and add the MS SQL service to the demo .NET app.<\/p>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Creating_a_service\"><\/span>Creating a service<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>First, we need to check if MS SQL is available in the &#8220;marketplace&#8221; of the PCF instance and what plans it has. If you run the same commands on PWS, it will display publicly available services with corresponding pricing.<\/p>\n<pre>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;> cf marketplace\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Getting services from marketplace in org Altoros \/ space dev as {UserName}...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OK\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;service \tplans     description\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;mssql-dev   default   Microsoft SQL Server service for application development and testing\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TIP:  Use 'cf marketplace -s SERVICE' to view descriptions of individual plans of a given service.\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;> cf marketplace -s mssql-dev\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Getting service plan information for service mssql-dev as {UserName}...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OK\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;service plan   description     \tfree or paid\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;default    \tShared SQL Server   free\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;><\/pre>\n<p><\/p>\n<p>Judging by the output, <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">mssql-dev<\/code> is available with the <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">default<\/code> plan. There is no need to specify any additional parameters for this service at this moment. We only need the service name, the service plan, and a name for the instance (let\u2019s use <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">samplewebappSQL<\/code>).<\/p>\n<pre>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;> cf create-service mssql-dev default samplewebappSQL\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Creating service instance samplewebappSQL in org Altoros \/ space dev as {UserName}...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OK\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;><\/pre>\n<p><\/p>\n<p>Now let\u2019s check that the service has been enabled successfully.<\/p>\n<pre>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;> cf services\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Getting services in org Altoros \/ space dev as {UserName}...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OK\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;name          \tservice \tplan      bound apps   last operation\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;samplewebappSQL   mssql-dev   default            \tcreate succeeded\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;><\/pre>\n<p>Looks like it\u2019s working.<\/p>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Modifying_the_NET_app\"><\/span>Modifying the .NET app<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>After this step, we can bind the MS SQL service to the demo app, but we would like to do some modifications to the app first.<\/p>\n<p>Parameters of bound services are available to apps via environment variables. Right now, we do not know which parameters will be provided, so we add an additional view and a related controller method (action) to the demo app. To do this, we need to modify the <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">HomeController.cs<\/code> and <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">Environment.cshtml<\/code> files. The examples below are the parts that need to be changed in:<\/p>\n<ul>\n<li><code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">HomeController.cs<\/code>:<\/li>\n<\/ul>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">public ActionResult Environment()\r\n    \t{\r\n        \tViewBag.Message = &quot;Your environment page.&quot;;\r\n \r\n        \treturn View();\r\n    \t}\r\n<\/pre>\n<ul>\n<li><em>Environment.cshtml<\/em>:<\/li>\n<\/ul>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">@using System.Collections\r\n \r\n@{\r\n\tViewBag.Title = &quot;title&quot;;\r\n\tIDictionary environmentVariables = Environment.GetEnvironmentVariables();\r\n}\r\n \r\n&lt;h2&gt;Environment Variables&lt;\/h2&gt;\r\n&lt;div class=&quot;environment&quot;&gt;\r\n\t@foreach (DictionaryEntry entry in environmentVariables)\r\n\t{\r\n    \t&lt;div class=&quot;item&quot;&gt;\r\n        \t&lt;div class=&quot;key&quot;&gt;@entry.Key&lt;\/div&gt;\r\n        \t&lt;div class=&quot;value&quot;&gt;@entry.Value&lt;\/div&gt;\r\n    \t&lt;\/div&gt;\r\n\t}\r\n \r\n&lt;\/div&gt;\r\n<\/pre>\n<p>Then, we add a new <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">ActionLink<\/code> to <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">_Layout.cshtml<\/code>:<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">&lt;div class=&quot;navbar-collapse collapse&quot;&gt;\r\n            \t&lt;ul class=&quot;nav navbar-nav&quot;&gt;\r\n                \t&lt;li&gt;@Html.ActionLink(&quot;Home&quot;, &quot;Index&quot;, &quot;Home&quot;)&lt;\/li&gt;\r\n \r\n                \t&lt;li&gt;@Html.ActionLink(&quot;Environment&quot;, &quot;Environment&quot;, &quot;Home&quot;)&lt;\/li&gt;\r\n \r\n                \t&lt;li&gt;@Html.ActionLink(&quot;About&quot;, &quot;About&quot;, &quot;Home&quot;)&lt;\/li&gt;\r\n                \t&lt;li&gt;@Html.ActionLink(&quot;Contact&quot;, &quot;Contact&quot;, &quot;Home&quot;)&lt;\/li&gt;\r\n            \t&lt;\/ul&gt;\r\n            \t@Html.Partial(&quot;_LoginPartial&quot;)\r\n        &lt;\/div&gt;<\/pre>\n<p>Now, we can publish the demo application to PCF, bind it to the MS SQL service, and view what environment variables are provided to us.<\/p>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Publishing_the_NET_app\"><\/span>Publishing the .NET app<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>We push <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">SampleWebApp<\/code> to the <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">Published<\/code> folder (as described in the previous post), using the same set of commands:<\/p>\n<pre>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;> cf push SampleWebApp -p .\\ -s windows2012R2 --no-start -b https:\/\/github.com\/ryandotsmith\/null-buildpack.git\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Using stack windows2012R2...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OK\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Creating app SampleWebApp in org Altoros \/ space dev as {UserName}...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OK\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Using route samplewebapp.cf-dev.altoros.com\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Binding samplewebapp.cf-dev.altoros.com to SampleWebApp...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OK\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Uploading SampleWebApp...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Uploading app files from: .\\\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Uploading 1.1M, 76 files\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Done uploading\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OK\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;> cf enable-diego samplewebapp\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Setting samplewebapp Deigo support to true\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\u2190[1;32mOk\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\u2190[0m\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Verifying samplewebapp Deigo support is set to true\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\u2190[1;32mOk\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\u2190[0m\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;><\/pre>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Binding_the_MS_SQL_service_to_the_NET_app\"><\/span>Binding the MS SQL service to the .NET app<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Then, we bind the MS SQL service instance, <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">samplewebappSQL<\/code>, to the demo app:<\/p>\n<pre>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;> cf bind-service SampleWebApp samplewebappSQL\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Binding service samplewebappSQL to app SampleWebApp in org Altoros \/ space dev as {UserName}...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OK\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TIP: Use 'cf.exe restage SampleWebApp' to ensure your env variable changes take effect\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;> <\/pre>\n<p><\/p>\n<p>Please keep in mind that not all CF commands are case-insensitive. For example, application-related commands recognize <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">samplewebapp<\/code> and <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">SampleWebApp<\/code> as the same application, but the service-related set of commands is case-sensitive, so you cannot use <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">SampleWebAppSQL<\/code> instead of <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">samplewebappSQL<\/code>.<\/p>\n<p>At this point, we can start our demo .NET app:<\/p>\n<pre>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;> cf start SampleWebApp\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Starting app SampleWebApp in org Altoros \/ space dev as {UserName}...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Creating container\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Successfully created container\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Downloading app package...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Downloaded app package (7.8M)\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Downloading buildpacks (https:\/\/github.com\/ryandotsmith\/null-buildpack.git)...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Downloaded buildpacks\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Staging...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Exit status 0\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Staging complete\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Uploading droplet, build artifacts cache...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Uploading build artifacts cache...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Uploading droplet...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Uploaded droplet (7.8M)\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Uploading complete\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0 of 1 instances running, 1 starting\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0 of 1 instances running, 1 starting\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 of 1 instances running\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;App started\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OK\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;App SampleWebApp was started using this command `..\\tmp\\lifecycle\\WebAppServer.exe`\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Showing health and status for app SampleWebApp in org Altoros \/ space dev as {UserName}...\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OK\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;requested state: started\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;instances: 1\/1\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;usage: 1G x 1 instances\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;urls: samplewebapp.cf-dev.altoros.com\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;last uploaded: Fri Jan 15 06:23:23 UTC 2016\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;stack: windows2012R2\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;buildpack: https:\/\/github.com\/ryandotsmith\/null-buildpack.git\r\n \r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \t   state     \t    since            cpu\tmemory     \tdisk      \tdetails\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#0   running   2016-01-15 09:33:04 AM   0.4%   296.4M of 1G   46.8M of 1G\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;><\/pre>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Getting_credentials\"><\/span>Getting credentials<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Open a new environment page and check the list of variables like it&#8217;s shown in the picture below.<\/p>\n<p><center><img decoding=\"async\" src=\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/dotnet-on-pivotal-cf.png\" alt=\".NET Applications on Pivotal CF: Binding MySQL\" width=\"630\" class=\"aligncenter size-full wp-image-45343\" \/><\/center><\/p>\n<p>We need the <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">VCAP_SERVICES<\/code> variable that contains all the information we need in the JSON format.<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">VCAP_SERVICES\r\n{&quot;mssql-dev&quot;:&#x5B;{&quot;name&quot;:&quot;samplewebappSQL&quot;,&quot;label&quot;:&quot;mssql-dev&quot;,&quot;tags&quot;:&#x5B;&quot;mssql&quot;,&quot;relational&quot;],&quot;plan&quot;:&quot;default&quot;,&quot;credentials&quot;:{&quot;hostname&quot;:&quot;10.92.0.109&quot;,&quot;host&quot;:&quot;10.92.0.109&quot;,&quot;port&quot;:1433,&quot;name&quot;:&quot;cf-0eb5ae1a-0e3c-467a-8ac3-4027ea96faf8&quot;,&quot;username&quot;:&quot;cf-0eb5ae1a-0e3c-467a-8ac3-4027ea96faf8-21e07dbd-a15a-4a81-a91b-eb756e3d46a7&quot;,&quot;password&quot;:&quot;EOr2tI2bW4o39U8-T81dcvqk5JMJD_uFvjuP5uhsPxY=Aa_0&quot;,&quot;connectionString&quot;:&quot;Address=10.92.0.109,1433;Database=cf-0eb5ae1a-0e3c-467a-8ac3-4027ea96faf8;UID=cf-0eb5ae1a-0e3c-467a-8ac3-4027ea96faf8-21e07dbd-a15a-4a81-a91b-eb756e3d46a7;PWD=EOr2tI2bW4o39U8-T81dcvqk5JMJD_uFvjuP5uhsPxY=Aa_0;&quot;}}]}\r\n<\/pre>\n<p>The credentials in this quote were automatically generated during service binding.<\/p>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Connecting_the_app_to_the_MS_SQL_service\"><\/span>Connecting the app to the MS SQL service<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>We could just replace the default connection string in the <code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">web.config<\/code> file, but we prefer this connection string to change dynamically during application startup. The examples below are the parts that we have changed in:<\/p>\n<ul>\n<li><code style=\"color: #222222; background-color: #e6e6e6; padding: 1px 2px;\">Web.config<\/code>:<\/li>\n<\/ul>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">&lt;appSettings&gt;\r\n\t\u2026\r\n\t&lt;add key=&quot;DBServiceName&quot; value=&quot;mssql-dev&quot;\/&gt;\r\n  &lt;\/appSettings&gt;<\/pre>\n<ul>\n<li><em>Startup.cs<\/em>:<\/li>\n<\/ul>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">public void Configuration(IAppBuilder app)\r\n    \t{\r\n        \tConfigureAuth(app);\r\n        \tConfigureDB(app);\r\n    \t}\r\n \r\n    \t\/\/\/ &lt;summary&gt;\r\n    \t\/\/\/ Get connection string from environment variables and replace it in web.config\r\n    \t\/\/\/ &lt;\/summary&gt;\r\n    \t\/\/\/ &lt;param name=&quot;app&quot;&gt;&lt;\/param&gt;\r\n    \tprivate void ConfigureDB(IAppBuilder app)\r\n    \t{\r\n        \tstring dbServiceName = ConfigurationManager.AppSettings&#x5B;&quot;DBServiceName&quot;];\r\n        \tstring vcapServices = System.Environment.GetEnvironmentVariable(&quot;VCAP_SERVICES&quot;);\r\n \r\n        \t\/\/ if we are in the cloud and DB service was bound successfully...\r\n        \tif (vcapServices != null)\r\n        \t{\r\n            \tdynamic json = JsonConvert.DeserializeObject(vcapServices);\r\n            \tforeach (dynamic obj in json.Children())\r\n            \t{\r\n                \tif (((string)obj.Name).ToLowerInvariant().Contains(dbServiceName))\r\n                \t{\r\n                    \tdynamic credentials = (((JProperty)obj).Value&#x5B;0] as dynamic).credentials;\r\n \r\n                    \t\/\/ replace connection string\r\n                    \tvar settings = ConfigurationManager.ConnectionStrings&#x5B;&quot;DefaultConnection&quot;];\r\n                        var fi = typeof(ConfigurationElement).GetField(\r\n                                  \t&quot;_bReadOnly&quot;,\r\n                                  \tBindingFlags.Instance | BindingFlags.NonPublic);\r\n                    \tfi?.SetValue(settings, false);\r\n                        settings.ConnectionString = credentials?.connectionString;\r\n \r\n                    \tbreak;\r\n                \t}\r\n            \t}\r\n        \t}\r\n    \t}<\/pre>\n<p>Repeat the publishing and deployment procedures, then start the application and test the connection to MS SQL by registering a new user. It works!<\/p>\n<p><center><img decoding=\"async\" src=\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/cloudfoundry-services-mysql.png\" alt=\"Cloud Foundry Services: Binding MySQL to .NET App\" width=\"630\" class=\"aligncenter size-full wp-image-45343\" \/><\/center><\/p>\n<p>See <a href=\"https:\/\/github.com\/Altoros\/Altoros-PCF.Net\" target=\"_blank\" rel=\"noopener noreferrer\">this GitHub repo<\/a> for the full versions of example files used in this tutorial.<\/p>\n<p>We hope this post has helped you to get started with adding services to .NET apps on PCF. In the <a href=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cf-scaling-an-app-on-diego\/\">next tutorial<\/a>, we will talk about scaling .NET applications to multiple instances.<\/p>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Further_reaading\"><\/span>Further reaading<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ul>\n<li><a href=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-connecting-and-pushing-an-app\/\" >.NET on Pivotal CF: Connecting and Pushing an App<\/a><\/li>\n<li><a href=\"https:\/\/www.altoros.com\/blog\/how-to-set-up-a-cloud-foundry-infrastructure-for-net-apps-in-minutes\/\">How to Set Up a Cloud Foundry Infrastructure for .NET Apps in Minutes<\/a><\/li>\n<li><a href=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cf-scaling-an-app-on-diego\/\">.NET on Pivotal Cloud Foundry: Scaling an App on Diego<\/a><\/li>\n<\/ul>\n<hr \/>\n<p><center><small>This post was written by <a href=\"https:\/\/www.altoros.com\/blog\/author\/roman-yurkin\/\">Raman Yurkin<\/a>, edited by <a href=\"https:\/\/www.altoros.com\/blog\/author\/sophie.turol\/\">Sophie Turol<\/a> and <a href=\"https:\/\/www.altoros.com\/blog\/author\/sophie.turol\/\">Alex Khizhniak<\/a>.<\/small><\/center><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Deploying a service broker<\/p>\n<p>In the previous post, we demonstrated how to deploy a .NET app to a Pivotal CF instance. However, the application is only partially operational\u2014it still doesn\u2019t have access to a database. In this tutorial, we will explain how to configure a MS SQL DB service and connect [&#8230;]<\/p>\n","protected":false},"author":88,"featured_media":45342,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"content-type":"","footnotes":"","_links_to":"","_links_to_target":""},"categories":[214],"tags":[873,28],"class_list":["post-45341","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-cloud-native","tag-pivotal-cf"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>.NET on Pivotal Cloud Foundry: Adding a MS SQL Service to an App | Altoros<\/title>\n<meta name=\"description\" content=\"Learn how to configure a MS SQL DB service and bind it to a .NET app on a private Pivotal CF using the commands also viable for Pivotal Web Services.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\".NET on Pivotal Cloud Foundry: Adding a MS SQL Service to an App | Altoros\" \/>\n<meta property=\"og:description\" content=\"Deploying a service broker In the previous post, we demonstrated how to deploy a .NET app to a Pivotal CF instance. However, the application is only partially operational\u2014it still doesn\u2019t have access to a database. In this tutorial, we will explain how to configure a MS SQL DB service and connect [...]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/\" \/>\n<meta property=\"og:site_name\" content=\"Altoros\" \/>\n<meta property=\"article:published_time\" content=\"2016-02-04T19:09:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-07-15T11:17:45+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/dotnet-on-pivotal-cf.png\" \/>\n\t<meta property=\"og:image:width\" content=\"646\" \/>\n\t<meta property=\"og:image:height\" content=\"481\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Raman Yurkin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Raman Yurkin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/\",\"url\":\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/\",\"name\":\".NET on Pivotal Cloud Foundry: Adding a MS SQL Service to an App | Altoros\",\"isPartOf\":{\"@id\":\"https:\/\/www.altoros.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/dotnet-on-pivotal-cf.png\",\"datePublished\":\"2016-02-04T19:09:30+00:00\",\"dateModified\":\"2019-07-15T11:17:45+00:00\",\"author\":{\"@id\":\"https:\/\/www.altoros.com\/blog\/#\/schema\/person\/e8d9865cc792d5c8d3c3346a4dccb305\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#primaryimage\",\"url\":\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/dotnet-on-pivotal-cf.png\",\"contentUrl\":\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/dotnet-on-pivotal-cf.png\",\"width\":646,\"height\":481},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.altoros.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\".NET on Pivotal Cloud Foundry: Adding a MS SQL Service to an App\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.altoros.com\/blog\/#website\",\"url\":\"https:\/\/www.altoros.com\/blog\/\",\"name\":\"Altoros\",\"description\":\"Insight\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.altoros.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.altoros.com\/blog\/#\/schema\/person\/e8d9865cc792d5c8d3c3346a4dccb305\",\"name\":\"Raman Yurkin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.altoros.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/roman-yurkin-96x96.jpeg\",\"contentUrl\":\"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/roman-yurkin-96x96.jpeg\",\"caption\":\"Raman Yurkin\"},\"description\":\"Raman Yurkin is Solutions Architect at Altoros. It is hard to list all the programming languages and technologies he has worked with over the last 20 years, but, today, his main focus areas are the .NET stack, Microsoft Azure, and PaaS.\",\"url\":\"https:\/\/www.altoros.com\/blog\/author\/roman-yurkin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":".NET on Pivotal Cloud Foundry: Adding a MS SQL Service to an App | Altoros","description":"Learn how to configure a MS SQL DB service and bind it to a .NET app on a private Pivotal CF using the commands also viable for Pivotal Web Services.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/","og_locale":"en_US","og_type":"article","og_title":".NET on Pivotal Cloud Foundry: Adding a MS SQL Service to an App | Altoros","og_description":"Deploying a service broker In the previous post, we demonstrated how to deploy a .NET app to a Pivotal CF instance. However, the application is only partially operational\u2014it still doesn\u2019t have access to a database. In this tutorial, we will explain how to configure a MS SQL DB service and connect [...]","og_url":"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/","og_site_name":"Altoros","article_published_time":"2016-02-04T19:09:30+00:00","article_modified_time":"2019-07-15T11:17:45+00:00","og_image":[{"width":646,"height":481,"url":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/dotnet-on-pivotal-cf.png","type":"image\/png"}],"author":"Raman Yurkin","twitter_misc":{"Written by":"Raman Yurkin","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/","url":"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/","name":".NET on Pivotal Cloud Foundry: Adding a MS SQL Service to an App | Altoros","isPartOf":{"@id":"https:\/\/www.altoros.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#primaryimage"},"image":{"@id":"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#primaryimage"},"thumbnailUrl":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/dotnet-on-pivotal-cf.png","datePublished":"2016-02-04T19:09:30+00:00","dateModified":"2019-07-15T11:17:45+00:00","author":{"@id":"https:\/\/www.altoros.com\/blog\/#\/schema\/person\/e8d9865cc792d5c8d3c3346a4dccb305"},"breadcrumb":{"@id":"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#primaryimage","url":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/dotnet-on-pivotal-cf.png","contentUrl":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/dotnet-on-pivotal-cf.png","width":646,"height":481},{"@type":"BreadcrumbList","@id":"https:\/\/www.altoros.com\/blog\/net-on-pivotal-cloud-foundry-adding-a-ms-sql-service-to-an-app\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.altoros.com\/blog\/"},{"@type":"ListItem","position":2,"name":".NET on Pivotal Cloud Foundry: Adding a MS SQL Service to an App"}]},{"@type":"WebSite","@id":"https:\/\/www.altoros.com\/blog\/#website","url":"https:\/\/www.altoros.com\/blog\/","name":"Altoros","description":"Insight","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.altoros.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.altoros.com\/blog\/#\/schema\/person\/e8d9865cc792d5c8d3c3346a4dccb305","name":"Raman Yurkin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.altoros.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/roman-yurkin-96x96.jpeg","contentUrl":"https:\/\/www.altoros.com\/blog\/wp-content\/uploads\/2019\/07\/roman-yurkin-96x96.jpeg","caption":"Raman Yurkin"},"description":"Raman Yurkin is Solutions Architect at Altoros. It is hard to list all the programming languages and technologies he has worked with over the last 20 years, but, today, his main focus areas are the .NET stack, Microsoft Azure, and PaaS.","url":"https:\/\/www.altoros.com\/blog\/author\/roman-yurkin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/posts\/45341","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/users\/88"}],"replies":[{"embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/comments?post=45341"}],"version-history":[{"count":5,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/posts\/45341\/revisions"}],"predecessor-version":[{"id":45348,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/posts\/45341\/revisions\/45348"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/media\/45342"}],"wp:attachment":[{"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/media?parent=45341"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/categories?post=45341"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.altoros.com\/blog\/wp-json\/wp\/v2\/tags?post=45341"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}