Home > Software design >  How to make a connection string for Microsoft SQL Server on another machine
How to make a connection string for Microsoft SQL Server on another machine

Time:07-06

I can't figure out how to make a connection string to access a database on another machine.

I'm using Visual Studio 2022 and when I copy the connection string from the server explorer it's pointing to the .mdf file on my computer, so that's clearly only going to work on my host machine (that is my computer).

Here's the connection string:

conn = new SqlConnection("Data Source=DESKTOP-42N6RDI; Initial Catalog=Database1");

I believe I don't need to specify the user id and the password.

I've tried to add the port, and I've also tried to add the ip instead of the server name but it just doesn't work.

The error I'm getting is always the same:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
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)

EDIT: It also says "Inner Exception"

Win32Exception: The system cannot find the file specified

Any help appreciated!

CodePudding user response:

Try using the connection string format described here. If you are on a domain and will be using the logged-in account on the client to access the SQL server you can use a trusted connection

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Otherwise you'll need to provide credentials.

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

CodePudding user response:

If there is no problem with your sqlserver configuration. If there is a problem, pay attention to the following:

  1. Open SQL Server Configuration Manager.
  2. Select Network Configuration TCP/IP to view the properties.
  3. Select IP1, IP2, IP3, IP4 to enable in the Enable column.
  4. After enabling, you need to restart the SQLSERVER service.

method to connect to database:

     public SqlConnection connect()
     {
         //database connection string
         string str = @"Data Source=DESKTOP-IIU0MPE;Initial 
         Catalog=BaoKan;Integrated Security=True";
         //create database connection object
         sc = new SqlConnection(str);
         //open database
         sc.Open();
         //return database object
         return sc;
     }

/* "Data Source=DESKTOP-IIU0MPE" in the database connection character, the equivalent content is the name of your own computer (you can right-click "This computer -> Properties" to view), "Initial Catalog=BaoKan" is equal to the content that needs to be connected The name of the database */

For security reasons, it is necessary to add a method to close the database connection, which is in the same class as the above method.

     public void DaoClose()
     {
         sc.Close();
     }

    //Control code includes read operation, update operation
     public SqlCommand command(string sql)
     {
         SqlCommand cmd = new SqlCommand(sql, connect());
         return cmd;

     }

     //database operation
     // update operation
     public int Execute(string sql)
     {
         return command(sql).ExecuteNonQuery();
     }

     // read operation
     public SqlDataReader read(string sql)
     {
         return command(sql).ExecuteReader();

     }
  • Related