1

I am trying to connect web api made in asp.net to sql server database provided by aws rds. I have never used aws before so I am not really sure if I am missing something there. I have tried to do it but i get following message when I added migration and trying to update database (using EF core):

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have following code in my API:

Context class:

 public class TestContext : DbContext
    {
        public virtual DbSet<Fruit> Fruits { get; set; }

        public TestContext(DbContextOptions<TestContext> options) : base(options) { }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("server=<nameofserver>;user=<username>;password=<password>;database=<nameofdatabase>;"); // there i put data from my database hosted on aws
            }
        }

and in Program.cs:

builder.Services.AddDbContext<TestContext>(options =>
                options.UseSqlServer("server=<nameofserver>;user=<username>;password=<password>;database=<nameofdatabase>;"))// there i put data from my database hosted on aws1;

I know I should put connection string in appsettings.json but I believe that is not the case now. Why isn't the table being created in the database? Should i enable/do sth on aws website? Or maybe the problem is in the code? How can I solve it?

Improve this question

2 Answers 2

Reset to default
0

It may or may not have anything to do with your connection string, but the very first thing you need to make sure of is that the server where your api/website is running has permissions to talk to your rds instance - by default it does not; you will need a security group that specifically allows it before you do anything else (and don't accidentally make it public - you rds instance will get hammered by hackers trying t guess the password).

Easiest way to check is fire up SSMS or another such tool, and see if you can connect to RDS from the machine that runs the code - if you can't - nothing you do in your code will solve that.

Once you can connect from SSMS to RDS successfully, then you can play around with your connection string if it still is having problems.

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

1 Comment

Actually i made it public but I am trying to figure out how this rws works because I will need it to host APP during the hackathon(So the security is not that important because it will be hosted maximum a couple of hours only for the contest usage). I do not have previous experience in hosting API. In this exaple above the created database on aws is empty and I am trying to connect with fresh Asp.net api to it and create one table.
0

At the current time, you will find a similiar use case for AWS SDK for .NET.

This current example shows you how to use the AWS SDK for .NET (v3) to create a REST service that lets you do the following:

  • Read, write, and update work items that are stored in an Amazon Aurora Serverless database.
  • Use Amazon Simple Email Service (Amazon SES) to send email reports of work items.
  • Create an AWS Secrets Manager secret that contains database credentials and use it to authenticate calls to the database. You can find this example in the AWS Code Library - which is the go to document for latest AWS SDK examples.

https://docs.aws.amazon.com/code-library/latest/ug/aurora_example_cross_RDSDataTracker_section.html

I believe this example will be ported to SQL Server at some point.

To connect to SQL Server, you need to supply your own connection details (unlike the Amazon Aurora Serverless database that needs other creds as discussed in the example.). Also note that SQL Server Does Not have a Service Client like Amazon Aurora Serverless does.

Also - make sure you set your inbound rules correctly.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.RDSSecurityGroups.html

Once you set your inbound rules, set the user password, etc, the following C# should work.

static void Main(string[] args)
        {
            try 
            { 
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

                builder.DataSource = "<your_server.database.windows.net>"; 
                builder.UserID = "<your_username>";            
                builder.Password = "<your_password>";     
                builder.InitialCatalog = "<your_database>";
         
                using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                {
                    Console.WriteLine("\nQuery data example:");
                    Console.WriteLine("=========================================\n");
                    
                    connection.Open();       

                    String sql = "SELECT name, collation_name FROM sys.databases";

                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
                            }
                        }
                    }                    
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
            Console.WriteLine("\nDone. Press enter.");
            Console.ReadLine(); 
        }
    }
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.