Can't access my SQL Server without SQL Engine-CodePudding

I have two computers. One is running SQL Server, and I can access the server using SQL authentication from the 2nd PC using SSMS.

I have created a C# Windows Forms application that connects to the database. However, I couldn't access my server from the application.

I disabled the firewall, allowed remote control, and allowed mixed mode authentication. I also forwarded required ports to my IP in my router settings.

I tried both these connecting strings, but they didn't help:

"Persist Security Info = False; User ID = gues; Password=gues;Initial Catalog = CoronaNurse; Server="   server;

"Data Source="   server   ";Initial Catalog=CoronaNurse;Integrated Security=false;UID=gues;Password=gues";

(server is a string that have IP of my server) (gues is a login in my Server)

The weird thing is when I login as gues in SSMS from my 2nd computer I can access the server in the first computer.

The question is, how do I access my server from a computer that doesn't have SSMS or any specific Login?

I need my application to be able to connect to my server without anything else installed, but I can't find where my problem is.

Adding from comments:

Im using the connecting to get a con string from my DB depends on the table i get with my gue.login function SqlDataAdapter

adapter = new SqlDataAdapter("select * from gue.login('"   textBox1.Text.Trim()   "', '"   textBox2.Text.Trim()   "', '"   server   "')", conn); 
SqlCommandBuilder cb = new SqlCommandBuilder(adapter); 
DataSet ds = new DataSet(); 
adapter.Fill(ds); 
string connection; 
connection = ds.Tables[0].Rows[0][0].ToString();

CodePudding user response：

Unless you haven't posted up all of your code, you don't appear to be controlling your SQL connection and I would strongly suggest that you use a parameterised call to protect against SQL injection from using direct text entry field values e.g.:

var dataset = new DataSet();

using (var connection = new SqlConnection(SqlConnectionString))
{
    connection.Open();
    var command = new SqlCommand("GetAll", connection);
    command.CommandType = CommandType.StoredProcedure;

    var adapter = new SqlDataAdapter(command);
    adapter.Fill(dataset);
    
    ...
}

CodePudding user response：

The SQL is wrong, and the connection strings look a little off. You might also need an instance name as part of the server. For example, instead of just localhost or 192.168.0.20, you might need localhost\SQLExpress or 192.168.0.20\..

One way you can find the connection string for sure is to use Visual Studio instead of SSMS to connect to the database. The Visual Studio Database Tools has a similar connection window as SSMS, and you can use it to show you the actual connection string it used.

When you've figured that out, try something more like this:

var connString = $"Server={server};Database=CoronaNurse;User Id=gues;Password=gues";

var sql = "select * from gue.login WHERE username = @username AND pHash = @pHash";
var ds = new DataSet();
using (var conn = new SqlConnection(connString))
using (var cmd = new SqlCommand(sql, conn))
using (var adapter = new SqlDataAdapter(cmd))
{
    cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = textBox1.Text.Trim();
    cmd.Parameters.Add("@pHash", SqlDbType.Char, 60).Value = BCrypt.Net.BCrypt.HashPassword(textBox2.Text.Trim());
    adapter.Fill(ds);
    var connection = ds.Tables[0].Rows[0].Items[0].ToString();
}

Note the use of both parameterized queries and BCrypt (you can add BCrypt via NuGet). There are a few things in database development that are too important to do wrong, even for proof-of-concept and learning projects. One of these is SQL Injection. Another is password handling. What I posted still isn't quite right for password handling (you should instead retrieve the stored hash and use the Verify() method), but it's close enough to set you on the right path.

CodePudding user response：

Thank you everyone I willl try to clear my self more

My function is fine it have 'where' inside and it is working fine, it returns one cell that have the conn string i need

If I disable the login "gues" in my second computer server And then try to connect with my application It show me login is disabled (remeber my server and login - another login with the same name- are placed on the first computer) But then if i enable that login in the second computer and connect with my application everything literally works perfect Why my application is related to that 2nd login in my second pc why it is connected to this. . My code is very huge I tried to share the related part thank you again for your patience

CodePudding user response：

someone has told me that I can't access my online SQL Server DB from a client PC that doesn't have Local DB I guess that explain my problem perfectly! I never knew that, in my case that is my problem right?