Cannot open database requested by login

Question

I have a SQL Server 2008 R2 Express database.

I have written a Delphi application that uses a TADOConnection object to connect to the database. It works just fine on my computer. It fails to connect from other computers on the network.

It gives the error message:

Cannot open database "PeopleNetLink" requested by the login.

I have found all sorts of questions about this error message. I have found none that used SQL Server authentication and worked on one computer and not on others.

My connection string is

Provider=SQLNCLI10.1;
Integrated Security="";
Persist Security Info=False;
User ID=PNetLink;
Initial Catalog=PeopleNetLink;
Data Source=W1CNVS02\SQLEXPRESSPAY12;
Use Procedure for Prepare=1;
Auto Translate=True;
Packet Size=4096;
Initial File Name="";
Use Encryption for Data=False;
Tag with column collation when possible=False;
MARS Connection=False;
DataTypeCompatibility=0;
Trust Server Certificate=False;
Server SPN=""

Further experimentation reveals that if I log on to another computer as an admin, the program will log in successfully. This tells me that it is not using the user ID specified in the connect string. Now the question is "why not?"

Can you ping W1CNVS02 from one of the other workstations? If so, check your firewall settings. Also can you open any other db on the server from one of the other machines? — MartynA
– MartynA, Commented Dec 11, 2015 at 19:40
The Express editions by default don't allow remote connections - you need to explicitly enable those. Have you done that? — marc_s
– marc_s, Commented Dec 11, 2015 at 21:08
It is apparently not using the User ID specified in the connect string. No idea why not. — WeststarEric
– WeststarEric, Commented Dec 11, 2015 at 23:38

MartynA · Accepted Answer · 2015-12-12 17:52:35Z

This is a bit of a long-shot, as I don't have Sql Server 2008 installed, only the 2014 version. And maybe you've tried this already, but I spent hours on this problem before I found the step I was missing.

Assuming a utility like MS's PortQry shows the server's local network firewall is open and that your edition of 2008 comes with a copy of Sql Server Management Studio installed:

Start SSMS on the server,
Right-click the Sql Server's Properties
In the Properties pop-up, click the Connections tab
Check the checkbox "Allow remote connections to this server."

In some Sql Server editions, "Allow remote connections ..." is not check by default, which seems to square with marc_s's comment.

Update: Regarding your added "This tells me that it is not using the user ID specified in the connect string.":

If you open the DFM as text and hand-edit the connection string so that the "User ID" has a completely bogus value, then go back to the connection string builder, you should find two things:

The Enter information to log on to server reverts to Use a specific user name and password, so if you then click Test connection, the test unsurprisingly fails.
If you then select Use Windows NT Integrated security and click Test connection again, the test succeeds.

From 2., it is evident that the "User ID" in the connection string is ignored when using Windows authentication. That doesn't seem surprising to me, since the point of using Windows authentication is that the credentials used are those of the logged-in Windows user. You've already established that if you log in on the other computer as an admin (I assume you mean a Windows administrator account), the connection succeeds, so I'd have thought that the non-admin account needs to be added to the Sql Server's recognised users if you're wanting to connect using that ID (though I confess it's not at all clear to me whether you're actually trying to connect using Windows authentication of a specific Sql Server account).

If you're still stuck try using e.g. the Microsoft OLE DB Driver for SQL Server instead of the SQL Server Native Client. You might also single-step through this article:

https://social.msdn.microsoft.com/Forums/en-US/523c7b7e-6216-4790-87cb-945f3c1f4c5e/can-not-connect-to-microsoft-sql-server-express-2012?forum=sqlexpress

Also, google

sql server express 2008 remote "secpol.msc"

and note articles such as this one

https://social.msdn.microsoft.com/forums/sqlserver/en-US/1f5221bf-f5c3-4307-836b-a4f9dc07f02f/very-strange-remote-connection-issues

Collectives™ on Stack Overflow

Cannot open database requested by login

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related