C# Sql connection string, trying to connect to local db from another computer

0
votes

I have a local SQL Server database that I'm trying to connect to via the connection string in my application in C#.

I have tried with this connection string, I have left Password blank because I have no password given in SQL login credentials: 

Data Source=myComputerIP,1433;Network Library=DBMSSOCN; Initial 
Catalog=myDataBase;User ID=myUsername;Password=;

I have enabled TCP/IP and started SQL Web Browser in SQL Server configuration manager, opened port 1433, SQL Server is configured to allow remote connections. I'm getting an error in C#

A network related or instance-specific error occurred while establishing the connection to the server.

Am I using wrong connection string?

My login to SQL Server DB: LOGIN CREDENTIALS

2
c#sql-serverconnection-string
can you check if your sql service is running or not ? you can check this by go to services and check if sql agent or sql server agent is running or not..... - piyush sanadhya
@piyushsanadhya Everything is running but the SQL Server Agent. I have set the start mode on Automatic and tryed to start the service in SQL Configuration Manager but got this error: "The request failed or the service did not respond in a timely fashion. Consult event log or other applicable error logs for details" - user2980341
can you force start the service , try restarting the system also sometimes that works too :) - piyush sanadhya
@piyushsanadhya Restarted system, still getting error. - user2980341

2 Answers

0
votes

You are using Windows Authentication so you must set also Integrated Security = true. Remove the User ID and Password.

With Integrated Security = true, the connection string will get the credentials of the user who runs the application

0
votes

from your attached PNG, it appears you're using SQLExpress. By Default this doesn't start on TCP port 1433. Check the ports in the Configuration Manager->Network Config->ProtocolsFor...-> right-click TCP/IP, choose properties and click IP Addresses tab

Scroll to the bottom to set TCP Port under IpAll section to 1433, restart SQL Server, then try again (or use whatever ports appear to be defined here in your connection string)

I'd also recommend using SQL Server users for remote clients rather than windows - this way it's nicely separated from windows and domains etc.

Make sure your SQL Server is configured for mixed mode: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode?view=sql-server-2017

you can then create a LOGIN (for the database server) and a USER (for the specific database you want them to access) via SSMS, or you could use this script:

-- Creates the login remoteuser with password 'Pa$$word11'.  
CREATE LOGIN remoteuser
    WITH PASSWORD = 'Pa$$word11';  
GO  

-- Creates a database user for the login created above.  
CREATE USER remoteuser FOR LOGIN remoteuser;  
GO 

--grant remoteuser 'datareader' rights
EXEC sp_addrolemember N'db_datareader', N'remoteuser';

--grant remoteuser 'datawriter' rights
EXEC sp_addrolemember N'db_datawriter', N'remoteuser';

You can do all the above through SSMS - add a login under the Security node, then in properties for the login set up database-level permissions too. The script is a bit more managable if you ever want to repeat the process though.

Connection string is then something like :

Data Source=192.168.0.55\SQLExpress;Database=MyDatabase;user=remoteuser;password=Pa$$word11