17

I have an SQL server 2016 instance running on Windows server 2016, I'm trying to connect to the DB from a remote PC in Visual Studio 2017 Community using the Server Explorer. The connection is successful and I can see the Databases available on the server and login just fine but when I try to expand the tables folder I get this error:

Failed to retrieve data for this request
Failed to connect to server (local)
Login Failed for user 'xxxxx'

The thing is I can connect just fine the same way using Visual Studio 2015 Enterprise means I can see the tables and alter them.

I made sure that the user had all the permissions granted on the server, And to me it doesn't seem to be a permission issue since the connection work just fine in VS 2015.

James Z
  • 12,209
  • 10
  • 24
  • 44
Fadi Banna
  • 554
  • 1
  • 10
  • 26
  • "Failed to connect to server (local)." Is pretty suspicious. Makes it sound like it trying to connect to a local SQLServer. – RBarryYoung Mar 28 '17 at 17:17
  • I don't this that is the case cause It connects to the server just fine and displays all the DBs in the server correctly , and also it adds the data connection just fine , the problem occurs just when trying to access the tables folder. the local thing could be due to the naming of the server FBSERVER.local, or I could be totally wrong and it does have something to do with this issue I can't tell. – Fadi Banna Mar 28 '17 at 17:56

6 Answers6

46

This would help. Make sure you check the "Save my password" checkbox when you add new database connection.

Add Connection Dialog with Save my password checked

Kiratijuta
  • 785
  • 7
  • 11
  • 1
    Wow I kind of gave up on this and here comes a solution that is so simple but weirdly it did solve the problem I couldn't have guessed that something like this would cause this kind of error. – Fadi Banna Apr 05 '17 at 13:46
  • 2
    This was exactly it. I had to add `Persist Security Info=True;` to my connection string in web.config –  Apr 05 '17 at 16:21
  • 1
    This worked for me. I was confused at first because I didn't receive the error in SQL Server Management Studio but this fixed it on Visual Studio. +1 for sure – Chris Gong May 31 '17 at 19:31
  • Thanks @kyaqb. Adding the 'Persist Security Info=True;' to my connection string worked. – Dickie Watkins Jul 04 '17 at 10:56
  • Far out, spent a long time switching screens for this issue, good work!! – Costas Aletrari Jul 14 '17 at 02:52
  • Add Connection wizard would temporarily solve the issue while the VS instance was open. However, adding 'Persist Security Info=True;' to the connection string AND running the Add Connection wizard, will precisely persist. Big Thank You! – Aday Jan 19 '18 at 00:45
6

For those who did code first migrations and have this issue, just right click on the connection name in server explorer and select 'Modify this connection...' then click on the save password tick box. Click OK and hey presto it all works again.

Simon Wales
  • 61
  • 1
  • 1
2

This could be due to MSSQL data source type. Switching to OLE DB data source type worked for me . I was using:-

Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 (X64) Jun 28 2018 17:24:28 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Datacenter Evaluation 10.0 (Build 14393: ) (Hypervisor)

The solution link. https://stackoverflow.com/a/20077395/8917467

Kman
  • 41
  • 9
0

Have you tried saving the Solution/Project and trying again? I've been wrestling with this on a new install of VS 2017 Community and that's what just worked for me. The .rds file wasn't updating until I saved all. After that it let me use the "add table" button again.

Scott Squire
  • 1
  • 1
  • Hello and welcome to StackOverflow. I see your answer begins with a question. Often people will point out that asking for more information is more appropriate for comments than answers. That said, I see you're also offering a solution that sounds like it worked for you and may well work for the poster of the question. Perhaps in the future be sure that your wording for answers avoids asking a question by instead wording the answer like: "If you haven't tried saving the Solution/Project and trying again perhaps you should give that a try. I've been...". – Louis Langholtz Mar 28 '17 at 21:51
  • 1
    It has nothing to do with any project or solution I try to connect through the server explorer without opening any project just adding a data connection it works on vs 2015 but not 2017 for some strange reason – Fadi Banna Mar 28 '17 at 21:58
0

I am having the exact same issue. loging failed when trying to expand the tables folder. all other folders actually work, Views, Stored Procedures but not Tables.

Mao
  • 41
  • 4
0

The issue is Visual Studio 2017 does more calls to load tables (in order to gather row counts) than 2015. You can see this in SQL Profiler. The issue is worse connecting to SQL Server 2016 due to "memory optimized tables" analytics.

The default timeout for the designer is 30 seconds. Assuming 20 ms SLA to AWS or Azure, a 6,000 object schema will ALWAYS time out in 2017 under default settings.

Unfortunately, increasing the timeout will not make things faster.

John Zabroski
  • 2,212
  • 2
  • 28
  • 54