74

When comparing an SQLServer project in Visual Studio 2015 Update 2 against a database SQL Server 2012, it shows the error "Target is unavailable" or "Source is unavailable" when change the direction.

It used to work properly a couple of months ago. Is there any workaround? I couldn't find any.

Sam
  • 40,644
  • 36
  • 176
  • 219
dave
  • 2,291
  • 3
  • 19
  • 25
  • There's an issue for this in Visual Studio Developer Community: https://developercommunity.visualstudio.com/content/problem/73094/ssdt-source-is-unavailable.html. I recommend up-voting the issue so it gets noticed and addressed. – Sam Nov 23 '17 at 03:25
  • It's 2019 and still an issue in both VS2015 and VS2017. Answer by Sam did not help. Answer by sotn helped but only once. Creating a new temporary DB user worked as a last resort workaround. – user5226582 Jan 31 '19 at 11:25

17 Answers17

123

I have found that using username@servername as the username for connecting will fix the issue when it happens against Azure databases. I've had connections that have historically worked without doing this suddenly stop and then this has fixed it for me.

For instance, when using a login of mylogin and connecting to myserver.database.windows.net, if I instead use mylogin@myserver.database.windows.net as the username I won't get the "Source is unavailable" issue.

Stacy Vicknair
  • 1,631
  • 1
  • 13
  • 12
  • 2
    This was great. Solved months of frustration!. I found it works with just username@myserver. I didnt need to provide the database.windows.net portion. – robs Nov 29 '16 at 17:10
  • Hey @robs, at first I did the same and didn't use the fully qualified server address. I found that the problem came back after a while when I did it and that's why I edited this post as I did. Just keep in mind that if the issue happens again you may need to go to the full server name. – Stacy Vicknair Nov 30 '16 at 17:42
  • You are genius! – Mark Sep 18 '18 at 02:55
  • Worked for me today on VS2017. – Etienne Juneau Oct 10 '18 at 12:49
  • I was having problems with `username@myserver.database.windows.net` (I got endless prompts about the Azure SQL Firewall despite my Client IP being listed in Azure Portal). When I changed it to `username@myserver` (without `database.windows.net`) it worked again. Weird. – Dai Dec 30 '19 at 20:10
91

I get this error in Visual Studio 2017 when I use SQL Server Authentication but don't save the password. I've tested these steps with Visual Studio 15.6.3 and SQL Server 12.0.5000.0

Fixing the error

This fixes the problem, but the problem comes back the next time I restart Visual Studio.

  1. Go to Tools -> SQL Server -> New Schema Comparison...
  2. Select either Select Source or Select Target
  3. Select Select Connection
  4. Under Recent Connections, find all connections that gave you this error
  5. For each of these, right-click the connection and select Remove from History
  6. Restart Visual Studio

Preventing the error from coming back

  1. Complete Fixing the error above
  2. When you are setting the connection details, check the Remember Password checkbox.

Reproducing the error

If you want to troubleshoot this error, here is how I managed to reproduce it.

  1. Go to Tools -> SQL Server -> New Schema Comparison...
  2. Select a source and target connection
  3. For at least one connection, use SQL Server Authentication but don't select Remember Password
  4. Press Compare
  5. Ensure it works without errors
  6. Restart Visual Studio
  7. Repeat steps 1-4, using the exact same connection details
Sam
  • 40,644
  • 36
  • 176
  • 219
  • Thanks to this, we were able to do the s**** schema compare in vs 2015. Thanks @Sam! – Jack Frost Mar 28 '18 at 07:03
  • Brilliant! Worked for me. I had noticed that not saving the password meant that the cached history item would remain "not saved" even if you try and check the box again, but didn't try removing it. Thanks! – pcdev Jul 12 '18 at 06:54
  • This almost worked for me as stated, after following your fixing the error list and re-opening VS all my history had reappeared and a subsequent connect failed again. What worked (this time at least) was to remove all history and then connect again immediately without closing the dialogue. Sadly I suspect I'll be back on this question again in a week or two.... – Matt Oct 08 '18 at 08:50
  • Cool, Didn't expect this would really help, but it works, Thnx.. Edit -- Mine was VS 2015.. – Irf Mar 05 '19 at 07:56
  • 1
    This solution worked perfectly for me with VS 2017. +1 – Yogi Sep 16 '19 at 16:48
  • It's 2020 and still this bug is happening. Thanks Sam for this fix! VS Studio Professional 2017 – jaderanderson Sep 01 '20 at 09:13
27

After trying everything here, this is what worked for me:

  1. Close VS instance.

  2. Delete the saved connection keys in

HKEY_CURRENT_USER\SOFTWARE\Microsoft\VisualStudio\14.0\ConnectionMruList

  1. Reopen VS and try again.
yakya
  • 4,559
  • 2
  • 29
  • 31
  • I had the same problem with the Source connection: "Source is unavailable" These steps resolved the error for me right away. – David Alpert Apr 10 '17 at 20:41
  • I'm also facing same issue and these steps resolved the error. Thanks a lot. – Hitesh Patel Jul 11 '17 at 09:16
  • 1
    FYI, deleting the connection keys resolved my issue in VS 2017. – Dave Mason Nov 29 '17 at 15:37
  • 3
    I'm using VS 2017 (15.6), the `ConnectionMruList` registry key doesn't even exist. Anyone know where it was moved to? – Sam Mar 20 '18 at 04:38
  • Using VS2017, this resolved the issue. The key did exist, under 14.0. VSs seem to share some resources between versions – user5226582 May 01 '18 at 10:48
  • 3
    Didn't work for me using VS2017, key not found in any of the 14.0x or 15.0x folders – pcdev Jul 12 '18 at 06:49
  • For VS2017: Regarding to [link](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/74d2b7f0-09f2-4ef3-935b-e0a1e0e8fb82/schema-compare-target-unavailable?forum=ssdt) it can be found below `HKEY_CURRENT_USER\Software\Microsoft\SSDT\ConnectionStrings` now. – Jana Weschenfelder Jul 24 '18 at 12:21
18

The other answers did not work for me as I was using SQL Server directly and not Azure but after checking the advanced properties of the connection string, I saw that the Authentication was set to "Not Specified" and Network Library was empty.

I changed them and it worked again. Rather strange as it let me select the database for the connection. It just would not let me compare schemas giving the mentioned "Source is unavailable" message.

Sql

Kuffs
  • 35,581
  • 10
  • 79
  • 92
  • 4
    That worked for me about 3 times, and now I'm back to square 1. I have to do a combo of 1) closing VS, 2) clearing the MRU list in the registry, and 3) the above. – LarryBud Jan 05 '17 at 02:34
  • @LarryBud This worked for me to, but I think we should provide some more background, [How to Clear MRU](http://stackoverflow.com/a/84797/2912011), and for [Visual Studio 2015](http://stackoverflow.com/questions/33943943/clear-recent-projects-list-in-visual-studio-2015/33945037#33945037) – David Rogers Jan 24 '17 at 15:43
  • 2
    This just happened again in VS2017. The authentication combo will no longer "stick" and is reset every time the connection is opened. What worked for me this time was removing the connection from the list, choosing a different connection then adding back the connection I actually wanted. – Kuffs May 30 '17 at 09:50
  • Removing and re-adding the connection worked for me as well – Shaun Rowan Aug 10 '17 at 21:32
  • This worked for me too in VS2017, BUT with a caveat, I had to create a second user in the DB server, and use that for the target. If your comparing 2 db's that are on the same server, then it appears you have to use 2 separate usernames and passwords, this means you can't use windows auth, as that will always auth from your desktop login, you must use SQL Auth. In my case, I cheated and used the SA account on the source, and the web app user on the target. – shawty Sep 14 '17 at 13:57
12

For me it was the \ symbol at the end of the server's ip address in the server name field that fixed it. Very strange.

e.g. instead of 10.10.10.10 i write 10.10.10.10\ and it connects ok.

Note: I'm using Visual Studio 2017.

kzaw
  • 855
  • 11
  • 7
  • This worked at first, but after restarting Visual Studio, the problem started happening again. – Sam Nov 23 '17 at 02:29
2

I have had mixed results with the other answers here. I was using a save compare file that another developer had put together. This is a standalone SQL Server and not an azure database. The compare would work sometimes and not others (giving the target is unavailable error). In my case the saved compare was just using the server name and not using a FQDN. When I changed to a FQDN it worked for me. I am not sure if the problem will come back again but thought I would add this information in case it is useful for others.

Mike
  • 3,462
  • 22
  • 25
  • OK. So now I am having the same problem again. I wish there was some kind of logging here to tell you what the problem is because the server/db is clearly available. – Mike Jul 06 '18 at 15:30
  • Was getting the error with the servername and the FQDN. Just tried the IP Address and it worked. Apparently some kind of Servername caching issue. Messing with the servername (or adding an alternate name) may resolve this for people. – Mike Jul 06 '18 at 16:06
  • 1
    This time it was failing again but then I removed the history, restarted VS and added the connection again (with a single case difference for a letter) and that worked. This is a great feature but is getting a little annoying to work around this problem – Mike Jul 13 '18 at 16:41
2

Although some solutions on this page worked for me sometimes, but not all the times. But this method that I am describing, is working for me most of the times

When you specify your server name specify the protocol and port as this

Servername: tcp:my-server-name,1443

My server name is Azure BTW

Ghassan Karwchan
  • 3,355
  • 7
  • 37
  • 65
1

I had this error when opening a previously saved .scmp file which worked successfully when it was initially created.

The problem was caused by missing password in the saved connection string. The source used integrated authentication, so VS was not complaining about that.

Selecting the target connection again did not help, probably because VS used the cached connection string.

I solved the issue by adding the Password argument into the connection string in the file. There are two locations where the target connection string is specified (XPaths shown below):

  • /SchemaComparison/TargetModelProvider/ConnectionBasedModelProvider/ConnectionString
  • /SchemaComparison/SchemaCompareSettingsService/ConfigurationOptionsElement/PropertyElementName[Name='TargetConnectionString']

After these edits I reopened the .scmp file and the comparison ran successfully.

Dark Daskin
  • 1,308
  • 1
  • 13
  • 22
  • I'm trying to use this answer but am unable to find these locations. Are these directory paths? – RonC Nov 17 '17 at 15:03
  • These paths are XPATHs in the .scmp file. I tried this and it did not resolve my problem. May work for others though. – Mike Jul 06 '18 at 15:38
1

I have tried in 2015 Visual Studio Professional version and I got this issue at the time of SQL data comparison and it worked for me when I use host name instead of IP address of databases. Hope this will address the issue.

Prabhas Kumar Mishra
  • 11
  • 4
  • It's unclear if this has anything to do with the question asked. It may have been better as a comment to the question. If you do not have enough rep to comment, see the [tour] for how to gain rep. –  Nov 13 '18 at 16:05
0

A process was blocking the database. After executing a kill [spid] it worked again.

dave
  • 2,291
  • 3
  • 19
  • 25
0

Same problem appears when user have no required permissions to perform schema comparison operation https://msdn.microsoft.com/en-us/library/jj889462(v=vs.103).aspx

AdvanTiSS
  • 336
  • 3
  • 8
0

I was referring to SQL server via IP and gave me this problem. I referred to it via its DNS name and problem was solved!

Not sure why! However, it worked like this :)

Evan Camilleri
  • 74
  • 6
0

I can get it to work when I right click on the database name in the SQL Server Object Explorer window and select Schema Compare from there. If I try to use the Schema Compare window that opens from the Tools menu, it never works.

user9791281
  • 11
0

I actually had the same problem in VS studio 2015. But as the database was on my PC I was using localhost instead of the actual name of the computer. I just manaully selected the computer server as in the proposition of visual studio itself and it worked.

esdras
  • 13
  • 4
0

None of the answers supplied here worked for me; I am using SQL Server and Visual Studio 2017. I was able to force a comparison by adding the server's IP address to my hosts file, then using that host name in the connection box.

pixelbath
  • 55
  • 8
0

In my case, I just restarted my machine, and it worked fine.

Yaman
  • 1,030
  • 17
  • 33
0

screenshot

my OS: windows 11

looking at the following program on my computer (attached), I right-clicked and "stopped" the duplicates of "SQL server (SQLEXPRESS)" from currently running.

I then attempted to open it again, and it gave me the same error as before... Although within a minute of doing that, the program launched normally like before.

Just thought I'd let you know of my "fix" in the event other people run into the same issue.

Willie Cheng
  • 7,679
  • 13
  • 55
  • 68
Michael Essex
  • 1
  • 1