How I do properly connect to an SQL server using Windows authentication?

QuenTin113

Hello,

I'm writing a program that connects to a local database and writes to it using QSqlDatabase, but I've been unable to get a working connection between my program and the server.

qdb = QSqlDatabase::addDatabase("QODBC");

qdb.setConnectOptions();
QString serverName = "KIRKWOOD\TESTCLOUD";
QString ipName = "tcp:10.150.94.197,1433";
QString dbName = "Puma";

QString connectionString =  QString("DRIVER={ODBC Driver 17 for SQL Server};Server=%1;Database=%2;Trusted_Connection=Yes;").arg(ipName).arg(dbName);

                            /*
                            "Driver={SQL Server};"
                            "Data Source=KIRKWOOD\TESTCLOUD;"
                            "Initial Catalog=Puma;"
                            "Integrated Security=True";
                            */

qdb.setDatabaseName(connectionString);


bool connectionSuccessful = qdb.open();

if (connectionSuccessful) {
    writeLogWithNewLine("Connected to database.");
}
else {
    writeLog("Failed to connect to database.");
    writeLogWithNewLine(qdb.lastError().text());
}

When I run this code I get the following error in my log:

"Failed to connect to database.
[Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: No connection could be made because the target machine actively refused it. [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired QODBC3: Unable to connect"

When I replace ipName with serverName I get this error:

"Failed to connect to database.
[Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired QODBC3: Unable to connect"

When I change the driver to "SQL Server" I get this error:

"Failed to connect to database.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). QODBC3: Unable to connect"

I've done a lot of research and made a lot of changes to my code, from changing the Driver type to changing the formatting of "Trusted_Connection=Yes" to "Integrated Security=True" but I've had no luck getting anything much different from these errors.

This is frustrating because in this same program I was able to connect to a dummy database using SQL authentication perfectly fine, and on top of that, using this same machine I'm able to connect to the database through Visual Studio's Server Explorer. I've even directly copy pasted the connection string from visual studio but still got nothing. Is there something else I need to do here?

I would greatly appreciate feedback.

Thank you.

dheerendra

Are you not passing the user name and password for SQL connection request ? Error indicates connection is refused. It means that your server is reachable but access does not exist..

Can you try connecting using some DBExplorer OpenSource tool(e.g toad) and see connection is going fine.

QuenTin113

Thank you for the quick reply. My server only allows Windows authentication, so I thought that meant I can't use a username and password since it's supposed to just use my Windows credentials.

Like I said, I already successfully connected using Visual Studio's Server Explorer. I didn't need a password or username for that, I selected Windows authentication and all it asked for was the Server Name and Database name.

Using that connection string fails with this error:

"Failed to connect to database.
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified QODBC3: Unable to connect"

And adding a driver gives it this error:

"Failed to connect to database.
[Microsoft][ODBC Driver 17 for SQL Server]Neither DSN nor SERVER keyword supplied [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute QODBC3: Unable to connect"

I could try to download another DBExplorer OpenSource tool though and see if that works.

dheerendra

Try setting the following.

"Driver={SQL Server Native Client 11.0};
ODBC3 instead of ODBC
Try to set the user name and password.

QuenTin113

@dheerendra

I figured out the problem. I switched the Server to "KIRKWOOD\\TESTCLOUD". I was missing a \ in the server name. Thank you so much though.

dheerendra

cool. You can move the issue to SOLVED state.