'Can't access my SQL Server without SQL Engine

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();
c++sharpsql-server


Solution 1:[1]

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);
    
    ...
}

Solution 2:[2]

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.

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Joel Coehoorn
Solution 2
Solution 3 ToddB

Related Questions