koobas.hobune.stream
Can't connect from Unity 3.1 to SQL Server Express
I am trying to connect to SQL Express from Unity using the following connection string.
"Server=string\\sqlexpress;Database=AmksPhaseII;UserID=string\barry;Password=mypasswd;Integrated Security=SSPI"
The connection attempt fails -- in fact, the request never even seems to arrive at the server (according to the SQL Express logs).
I have everything running locally on my laptop, including SQL Express.
The code works fine directly out of a Mono application built with Mono Develop.
However, when I try to connect with Unity, using the exact same code and connection string, nothing happens. The SQL Express logs indicate that the login request never even arrives at the server (at least, there is no record of a failed login attempt, even though I've configured it to log both successful and failed login attempts).
The exception that I get back on from calling dbcon.Open() is: "An existing connection was forcibly closed by the remote host."
I get the same exception if I completely shut down SQL Express and then try to connect.
I have tried this with my firewall turned off -- that didn't make any difference.
It's possible that I don't have the right System.Data.dll in my Assets/Assemblies folder, but everything compiles o.k. I am using c:/Program Files (x86)/Unity3.1/Editor/Data/Mono/lib/mono/2.0/System.Data.dll.
There is more detail in a message I posted to the Forum at:
http://forum.unity3d.com/threads/91005-Can-t-connect-from-Unity-3.1-to-SqlExpress
Questions:
Is there any problem using SQL Express (as opposed to SQL Server) with Unity?
I am using Windows Authentication in both cases (Mono app. and Unity) -- could that be the problem?
Does the exception I'm receiving indicate that perhaps I'm not using the right port?
Any suggestions/ideas???
THANK YOU for any help! Barry
After reviewing the documentation on Plugins, I'm not sure that approach is destined to help. From the documentation, it is targeted primarily at accessing unmanaged code (e.g., c libraries). As far as I can tell, dropping a C# assembly in Assets/Plugins is no different from dropping it in Assets/Assemblies.
Thanks, Barry
PROBLEM SOLVED (or at least circumvented).
I got access to a different database -- a full SQL Server 2008 database. I changed my connection string to point at that database, then tried connecting first from my standalone application (success) and then from within Unity (success!).
I don't know specifically why I couldn't connect to my local SQL Server Express database, but presumably it was for one of the following reasons.
Unity might not work with SQL Server Express.
Unity might not work with Windows Authentication (I used SQL Server Authentication to connect to the Amazon-hosted server).
There could be something about my local environment which prevents Unity from connecting to my database.
So I never did really solved the issue of connecting to my local database. However, ultimately we just need to connect to our customer's database and now I'm fairly confident that that can be done.
BTW, it seems to work fine with the default System.Data assembly -- I didn't have any System.Data.dll in my Unity project hierarchy. Here's the line from the Console log file.
Platform assembly: C:\Program Files (x86)\Unity3.1\Editor\Data\Mono\lib\mono\2.0\System.Data.dll (this message is harmless)
This was all done with Unity 3.3.0f4.
Barry
You could put your database connection stuff in a .net assembly and use it as a plugin (not Assets/Assemblies but Assets/Plugins) and use it from script through its interfaces.
Hi Tom,
Thank you! Seems like a great suggestion. I tried it, however, and am still getting the same error message.
The DLL I created to access the database this time was a standard .NET DLL (i.e., as opposed to a Mono version) -- i.e., I compiled it with Visual Studio 2010, which presumably uses the .NET version of System.Data, etc. As before (but this time in VS2010 instead of MonoDevelop, and using a standard .NET connection string instead of the Mono version), I can connect using my library if I run it from a small standalone test program, but loading that same library into Unity and calling it from there (via a small script attached to an empty object) results in failure:
Exception: An existing connection was forcibly closed by the remote host.
In short... no luck yet.
Thanks, Barry
Don't post your comments as 'answers'. Rather comment on tom's post if you have something to discuss about it :p
I used Tcpview.exe to monitor network activity and found out a bit more about what is (and isn't) going on.
The bottom line is that I can't get Unity to attempt a TCP/IP connection at all -- at least, it doesn't get far enough to actually show anything in Tcpview. It does show a brief "SYN_SENT" packet when I hit "Play", but it does that regardless of the Unity application (even Bootcamp sends that :-)).
My Mono app works because it apparently uses Shared Memory -- it succeeds without showing anything in Tcpview. Based on that theory, I tried a few different URLs -- the following were all successful from my standalone application.
o "Server=string\\sqlexpress;Database=AmksPhaseII;Integrated Security=SSPI;User ID=string\barry;" o "Server=lpc:string\\sqlexpress;Database=AmksPhaseII;Integrated Security=SSPI;User ID=string\barry;" o "Server=tcp:string\\sqlexpress,58649;Database=AmksPhaseII;Integrated Security=SSPI;User ID=string\barry;" o "Server=string\\sqlexpress,58649;Database=AmksPhaseII;Integrated Security=SSPI;User ID=string\barry;"
The last two did force the application to use TCP/IP -- I could see it in Tcpview.
Unfortunately, they (and other connection strings) all failed when I tried them from within Unity. My best (and only) guess at this point is that perhaps Unity doesn't understand database instances (and hence doesn't understand the "\sqlexpress" part of the connection string), but I think that's a long shot.
Any ideas from anyone???
Thanks, Barry
P.S. It turns out that the fields available for Mono applications are just a subset of those available for .NET, so I went ahead and built a .NET app using my library instead of a Mono app. I'm still using the exact same code in Unity, but with Unity's Mono environment, of course.
I would like to update this page to reflect my findings on this subject. Like Barry I also had the same troubles connecting to a local instance of SQL Express. After reading Barry's report I realized the trouble is in the named instance.
If you install SQL Express as a Default Instance (meaning the named instance is MSSQLSERVER) then you can get around this problem. It seems that Unity cannot deal with the named portion of the connection string. This is a special instance in SQL Server Express that allows you to connect via Data Source=localhost with no name.
