1

I have the credentials to access a server in a local network - a network that is behind a proxy. And that server is running a SQL Server 7.0 database. The database is configured to use Windows authentication for log in.

I use the Remote Desktop Connection to access the server, put in the credentials, and when inside I open the Query Analyser, select log in with Windows authentication and then I query the DB.

But, now I need a .NET C# program to access the database from my machine, remotely. I have checked for a TCP/IP connection on port 1433 and it's open. However, with the following code, I cannot open a connection to the remote database.

SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder();
connStringBuilder["Trusted_Connection"] = false;
connStringBuilder["user id"] = "<domain>\\<user>";
connStringBuilder["password"] = "<pass>";
connStringBuilder["Database"] = "<db>";
connStringBuilder["Server"] = "\\\\<servername>\\MSSQLServer,1433";
connStringBuilder["Connection Timeout"] = "30";

SqlConnection myConn = new SqlConnection(connStringBuilder.ConnectionString);
try
{
    myConn.Open();
    // success
    Console.WriteLine("Connection Opened");
}
catch (Exception e)
{
    // failed
    Console.WriteLine("Connection Failed");
    Console.WriteLine(e.ToString());
}

The servername field is the same text I put in the Remote Desktop Connection tool, as it is with the user id and password fields after a connection is established (normal log on with Windows Server 2000).

Also, with the SSMS (SQL Server Management Studio) I cannot connect to the instance, however with Windows Explorer I can browse the server's drives with \\servername\e$ (for example).

Thank you in advance!

EDIT 1

I believe the problem is go through the remote machine log in, but I have no idea how to do that. I tested on a local db on my machine and the program works like a charm.

The error message from the exception e is the following, weather with true or false on the Trusted Connection:

System.Data.SqlClient.SqlException: 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: SQL Network Interface, error: 25 -
Connection string is not valid)
at .... etc

EDIT 2

The steps I take to log on to the machine using some credentials are the following:

I first put in the server name as in "MACHINE33" (without quotes)

Remote Desktop Connection

And then I put the credentials \ as in "me\johndoe"

Credentials to access the remote machine

In the end the database is accessed using the windows authentication used to access the machine.

Duarte Patrício
  • 133
  • 1
  • 10
  • You can not connect the instance with SQL Server Management Studio, so you should check the setting on your remote SQL Server first – Matt Aug 12 '14 at 15:46
  • Are you 100% sure the Sql Server instance has TCP/IP enabled? Check this via `Sql Server Configuration Manager` on the server itself. – mxmissile Aug 12 '14 at 15:46
  • If you have the default instance of SQL installed, you shouldn't need the \MSSQLServer after the server name. Also, check that port 1433 is open on the firewall otherwise you won't be able to connect. Log onto the server using Remote Desktop, and check the SQL configuration allows remote connections. – Jon Aug 12 '14 at 15:49
  • 1
    Make sure you have a firewall exception for port 1433. Also, I don't think \\ is valid in the connection string. It's usually just `[machineName]\[InstanceName],[port]`. – Arian Motamedi Aug 12 '14 at 15:55
  • What version Sql Server is this? – mxmissile Aug 12 '14 at 15:56
  • @mxmissile The version is 7.0 (I'll update the post, sorry), so there's no SQL Server Configuration Manager. – Duarte Patrício Aug 12 '14 at 16:31
  • @Mangist I believe the port is open on the firewall, since I used a netstat -a -n |find/i "1433" and found some connections established between that port. Also, to check in it accepts remote connections, I went to the Server Network Utility (SQL Server 7.0) and there I have a configuration for TCP/IP with the machine name and the port (the famed 1433). – Duarte Patrício Aug 12 '14 at 16:34
  • Did you drop the \MSSQLServer from the connection string, and just use the server name by itself? – Jon Aug 12 '14 at 16:36
  • @Mangist Yes, just did and it's still the same.@PoweredByOrange And I have to use the double slash - \\ - or it give build errors. – Duarte Patrício Aug 12 '14 at 16:41
  • If you're logging in with a Domain\User then you need to set Trusted Connection = true. Otherwise if you're just using a SQL login, you should have this set to false. Can you past the exact error message from the Exception e? – Jon Aug 12 '14 at 16:50
  • @Mangist I've posted an edit of the question with the error from the exception. Yes, I believe the Trusted Connection has to be true, but the domain/user that accesses the database is the one for the remote machine, not my local one. – Duarte Patrício Aug 12 '14 at 17:08
  • Can you put a breakpoint on this line, and post the contents of this string: connStringBuilder.ConnectionString, or do a Debug.WriteLine(connStringBuilder.ConnectionString); Thanks – Jon Aug 12 '14 at 17:12
  • Try using this as the connection string "Data Source=; Initial Catalog=; User=\; Password=; Trusted Connection=SSPI;" You may need to escape the \\ in the username. – Jon Aug 12 '14 at 17:13
  • Sql Server 7 did not support instances, you need to remove them from the connection string. – mxmissile Aug 12 '14 at 17:20
  • @Mangist Thank you for the help so far. The contents of the string were "Data Source=;Initial Catalog=;Integrated Security=False;User ID=\;Password=;...". I have tried again without \\ (only ) and the error is different. Just says the log in failed for the user. As for using the connection string directly, I always have a problem saying "Login failed for user '(null)'". However, it doesn't say null with ConnectionStringBuilder.@mxmissile Thank you for the tip. The errors are turning different now and I believe we're making progress. – Duarte Patrício Aug 12 '14 at 17:42
  • @Duarte, then you need to set the Integrated Security back to SSPI. Or set Integrated Security to true and not specify a userid / password (it would then take the currently logged in user). Or leave it as false but then you have to change the credentials to SQL Server credentials, not domain credentials – user469104 Aug 12 '14 at 17:44
  • If you are specifying \ then you HAVE TO set Trusted Connection=SSPI; Is your SQL server using a Windows Connection, or SQL credentials? You need to understand the difference. Google it. Also try to login to Managemnet Studio to , and leave the radio button on "Use Windows Authentication". If it works, great, use your Windows credentials as the \ and – Jon Aug 12 '14 at 17:47
  • @user469104 and@Magist I have changed it back to sspi and also updated the post with the whole steps. My time with the server is done today, so tomorrow I'm going to try again the new solutions. Thank you very much. – Duarte Patrício Aug 12 '14 at 17:58

2 Answers2

0

If you are using integrated authentication then the value for trusted connection should be either 'true' or 'sspi', not false. If 'true', any user id and password you specify on the connection string will be ignored so might as well not set them if you are intending to use the credentials for the currently logged in user (or the AppPool user if a web app, or the service user if a windows service etc.).

Are you using a named SQL Server instance? If so, a dynamic port is used, not 1433. You would need to either configure SQL Server to use a specific port for the named instance, or not specify a port.

"Login failed for user" when specifying default port

Community
  • 1
  • 1
user469104
  • 1,206
  • 12
  • 15
  • Yes, it's true about the Trusted Connection attribute. First I did tried with "true" and "sspi" but the results were the same. I changed to "false" because I started adding the credentials to accesss the remote machine. – Duarte Patrício Aug 12 '14 at 16:36
  • Just as an FYI, you cannot specify domain credentials if you are using 'false' for the trusted connection parameter, just SQL Server credentials. If you want to specify domain credentials different from the currently logged in user you would need to use 'sspi'. Or perhaps you were trying with SQL Server credentials? The example you provided says 'domain\\user' why I was under the impression that you were trying to specify a domain user. – user469104 Aug 12 '14 at 17:33
0

I have asked the systems administrator, this morning, and he told me that the database cannot be accessed from the outside, even though there is a TCP/IP configuration on port 1433 (checked under Server Network Utility for SQL Server 7.0). The reason is that the whole network is behind a proxy that checks all incoming requests and prevents the ones not coming from the tools specified (i.e. Remote Desktop Connection).

Since what I want is to migrate some fields from the structure of the old database to a new one, used by a new system, the solution is:

Access the remote machine, create a backup of the database, access the files through mapping the network drive, copy the backup files to your machine, create a local database and restore the backup to your new local database.

It's not the most elegant way, but it's the only way. And as an advice, first ask the systems administrator if you're behind a network full of proxies because it can save you a lot of headaches.

Thank you and sorry for the whole trouble.

Duarte Patrício
  • 133
  • 1
  • 10