How to connect MS SQL Server database from ASP.NET Core 2.2 hosted in 1&1 Ionos hosting server?

Question

I am developing a web application with asp.net core 2.2 and angular 7. My application is working locally but when I upload the application and SQL Server database to "1&1 Ionos" hosting server, it can't connect to the database there.

I tried with the connection string they provided in their database admin panel but it just not working.

Here is the appsettings I used in my application.

{
  "AppSettings": {
    "Secret": "bla bla bla"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "CustomPosConnection": "Provider=sqloledb;Data Source=db784146660.hosting-data.io,1433;Initial Catalog=myDbName;User Id=myUserId;Password=myPass;"
  }
}

Here "myDb" ,"myUserId","myPass" is equivalent to actual database name ,actual user id they provided and actual password that i set when creating database in the hosting server.

Please help me to connect the database in 1&1 ionos hosting server by suggesting write connection string or guiding me what else I need to do or please share if anyone has example connection string for connecting to SQL Server database from asp.net mvc or asp.net web api or asp.net core application hosted in 1&1 Ionos windows server.

you should write your servername or IP address for Data Source value, you dont need write sql port in connectionstring. This is an example: "Data Source=192.168.5.17;Initial Catalog=DatabaseName;Integrated Security=false;UId=sa;pwd=123456;MultipleActiveResultSets=True" — hassan.ef
– hassan.ef, Commented May 13, 2019 at 5:06
Do not use Integrated Security = false. It probably won't work. Use Integrated Security = true and eliminate the user name and password. The SQL Server defaults to using Windows credentials (true) which uses the users windows account. Normally I create a Windows Group Account and set the database to use the group account. Then add uses to Group so multiple uses have access to the database. In a corporate network that uses Group Policy the group accounts already exist. — jdweng
– jdweng, Commented May 13, 2019 at 5:48

Tofail Alam Tushar · Accepted Answer · 2019-05-13 07:16:06Z

1

At last it worked,here is my working appsettings. Hopefully it will help you guys.

  "AppSettings": {
    "Secret": "bla bla bla"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "CustomPosConnection": "Data Source=db784146660.hosting-data.io,1433;Initial Catalog=myDb;Integrated Security=False;User Id=myUserId;Password=myPass;MultipleActiveResultSets=True;"
  }
}

Here "myDb" ,"myUserId","myPass" is equivalent to actual database name ,actual user id they provided and actual password that i set when creating database in the hosting server.

answered May 13, 2019 at 7:16

Tofail Alam Tushar

311 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Panagiotis Kanavos Over a year ago

It won't help because it's not clear how this differs from the question's connection string. You don't need to add Integrated Security=False;, that's false by default. The only real difference seems to be MARS=True, but even that won't prevent you from connecting. 1433 is the default port so there's no need to specify it either

Tofail Alam Tushar Over a year ago

it just worked for me that's why i thought to share it.

Panagiotis Kanavos Over a year ago

What worked? The only meaningful change seems to be removing the meaningless Provider=sqloledb;. This would be needed if you used OledbConnection but not SqlConnection.

Sam Patirage · Accepted Answer · 2020-07-04 06:02:50Z

0

It has been hard to figure out the connection string for .Net core shared hosting. My provider AspHostPortal worded with

"DefaultConnection": "Server=ipAddress\\SQL2017,782; Database=DatabaseFileName_without.mdf;Integrated Security=False; User Id=userName;Pwd=passWord;MultipleActiveResultSets=true"

Integrated Security=True may not work if SSL is not enabled on server

I am not sure why Microsoft change the format from Web.Config format to confuse everybody.

edited Jul 4, 2020 at 6:02

answered Jul 4, 2020 at 5:57

Sam Patirage

1195 bronze badges

Collectives™ on Stack Overflow

How to connect MS SQL Server database from ASP.NET Core 2.2 hosted in 1&1 Ionos hosting server?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related