SQL Server connection string in Appsettings.json + .net Core 3.1

Question

I'm trying to setup and connect to my remote development SQL Server (SQL 2017) in appsettings.json within .NET Core 3.1, I have tried the following approaches yet without any success.

 "ConnectionStrings": {
//"DefaultConnection1": "DATA SOURCE=[servername];UID=[username];PWD=[password];DATABASE=[databasename]",
//"DefaultConnection2": "Data Source=[servername]; Initial Catalog=[databasename];User ID=[username];Password=[password]",
//"DefaultConnection3": "DataSource=[servername];Initial Catalog=[databasename];User Id=[username];password=[password]",
"DefaultConnection4": "Server=[servername];Database=[databasename];User Id=[username];password=[password];Trusted_Connection=False;MultipleActiveResultSets=true;" 
},

Error of DefaultConnection1:

ArgumentException: Keyword not supported: 'uid'.
Microsoft.Data.Sqlite.SqliteConnectionStringBuilder.GetIndex(string keyword))

Error of DefaultConnection2:

ArgumentException: Keyword not supported: 'initial catalog'. Microsoft.Data.Sqlite.SqliteConnectionStringBuilder.GetIndex(string keyword)

Error of DefaultConnection3:

ArgumentException: Keyword not supported: 'initial catalog'. Microsoft.Data.Sqlite.SqliteConnectionStringBuilder.GetIndex(string keyword)

Error of DefaultConnection4:

ArgumentException: Keyword not supported: 'server'.
Microsoft.Data.Sqlite.SqliteConnectionStringBuilder.GetIndex(string keyword)

Any ideas, pointers or help is much appreciated.

did you use a placeholder like ([servername]) in the connection string, or just because of security reasons? I mean in your real appsettings, you explicitly have written the server IP or server name? — Matt Ghafouri
– Matt Ghafouri, Commented Jan 22, 2021 at 10:24
Hi Majid, these are just placeholder for security reasons, in the actual file, i used the correct credentials and able to log in via SSMS. — DevCentral
– DevCentral, Commented Jan 22, 2021 at 10:27
I wonder though if the error is occurring due to "Microsoft.Data.Sqlite.SqliteConnectionStringBuilder" — DevCentral
– DevCentral, Commented Jan 22, 2021 at 10:28

DevCentral · Accepted Answer · 2021-01-22 11:37:00Z

2

thanks for your help and advice, i might have just found the solution and am able to connection the SQL database successfully now, the problem was as following:

The following package was installed on the solution:

Microsoft.Data.Sqlite.SqliteConnectionStringBuilder

in Startup.cs the following line was used in the ConfigureServices(IServiceCollection services):

services.AddDbContext<ApplicationDbContext>(options => options.UseSqlLite(Configuration.GetConnectionString("DefaultConnection")));

However after research i found out that i should rather use the following line:

services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Which was not accessible due the following package missing:

Microsoft.EntityFrameworkCore.SqlServer

After installing this package via package Manager using the following command:

Install-Package Microsoft.EntityFrameworkCore.SqlServer

I was able to access the SQL server using the following connection string:

  "ConnectionStrings": {
    "DefaultConnection": "Server=[servername];Database=[databasename];Persist Security Info=True;User ID=[username];Password=[password];MultipleActiveResultSets=True;"
  },

For safety, as Christian pointed out, I removed also the other connectionstring references.

edited Jan 22, 2021 at 11:37

answered Jan 22, 2021 at 11:15

DevCentral

1,1691 gold badge11 silver badges26 bronze badges

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

1 Comment

Pepe Alvarez Over a year ago

Since you are going to use a user name and a password you will need to add the next parameter to the connection string: "Integrated Security=True", this will make sure to use those parameters at the moment to make the connection, otherwise you will get an error.

Gerry · Accepted Answer · 2022-10-29 20:37:57Z

1

Your setup in appsettings.json is fine.

Suppose the model in your cs file is mytestModel. Here is the code to read the connection string:

public class mytestModel : PageModel
{
    public string connectstring = "";

    private IConfiguration MyConfiguration;
    public mytestModel (IConfiguration _configuration)
    {
        MyConfiguration = _configuration;
    }
    public void OnGet()
    {
        connectstring = this.MyConfiguration.GetConnectionString("DefaultConnection4");
    }   
}

It should be noted that the type of model (eg. PageModel) does not matter. The name of the model is what matters.

answered Oct 29, 2022 at 20:37

Gerry

473 bronze badges

Comments

Christian · Accepted Answer · 2021-01-22 10:31:50Z

0

Probably it's just because you're not supposed to put comments (//) in JSON as JSON is a data format and doesn't support comments. So try actually removing the lines starting with // from your config.

answered Jan 22, 2021 at 10:31

Christian

1,2589 silver badges12 bronze badges

1 Comment

NovaDev Over a year ago

Microsoft does have a json file format that does support comments. I'm not sure if that's a blessing or a curse, but they have it.

Collectives™ on Stack Overflow

SQL Server connection string in Appsettings.json + .net Core 3.1

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related