Search Unity

  1. Megacity Metro Demo now available. Download now.
    Dismiss Notice
  2. Unity support for visionOS is now available. Learn more in our blog post.
    Dismiss Notice

Can't connect from Unity 3.1 to SqlExpress

Discussion in 'Editor & General Support' started by Barry Holroyd, May 28, 2011.

  1. Barry Holroyd

    Barry Holroyd

    Joined:
    Mar 21, 2009
    Posts:
    17
    Hi Everyone,

    I've been struggling to connect to a local instance of SqlExpress. I've read through the various postings and tried a number of things, but still can't connect. I suspect the problem is the connection string I'm using. I understand that the connection string Mono requires is different than the one MS .NET requires. Using the Mono version and running my code in MonoDevelop, I'm able to connect just fine. However, the same code, when run in Unity, fails to connect.

    I've seen code posted in the forums which the authors say works (in fact, the code I'm using is a cut-and-paste from one of the postings). The only difference of significance that I can see is that I'm trying to access SqlExpress, which typically requires "\sqlexpress" after the hostname in Server. Perhaps Unity doesn't like SqlExpress for some reason???

    Here's what my personal trace is showing (it prints out the connection string I'm using, followed by the exception message).

    Connecting to the database(Server=string\sqlexpress;Database=AmksPhaseII;User ID=string\barry;Password=ttpp1357.;Integrated Security=SSPI)
    Exception: An existing connection was forcibly closed by the remote host.​

    Here's my complete code, as it appears in Unity. What am I doing wrong???

    public class Test2
    {
    public void Run(Logger l)
    {
    string connectionString =
    "Server=string\\sqlexpress;" +
    "Database=AmksPhaseII;" +
    "User ID=string\\barry;" +
    "Password=pswd;" +
    "Integrated Security=SSPI";
    l.Log("Connecting to the database(" + connectionString + ")");
    Console.WriteLine("Connecting to the database(" + connectionString + ")");
    //Console.WriteLine ("Connecting to the database({0})", connectionString);
    try
    {
    IDbConnection dbcon;
    using (dbcon = new SqlConnection(connectionString)) {
    dbcon.Open();
    using (IDbCommand dbcmd = dbcon.CreateCommand()) {
    string sql =
    "SELECT BinName " +
    "FROM Bin";
    dbcmd.CommandText = sql;
    using (IDataReader reader = dbcmd.ExecuteReader()) {
    while(reader.Read()) {
    string BinName = (string) reader["BinName"];
    l.Log("Name: " + BinName);
    Console.WriteLine("Name: " + BinName);
    }
    }
    }
    }
    }
    catch (Exception ex)
    {
    l.Log("Exception: " + ex.Message);
    Console.WriteLine("Exception: " + ex.Message);
    }
    Console.ReadLine();
    }
    }​

    Thank you for any help!

    Barry
     
  2. Barry Holroyd

    Barry Holroyd

    Joined:
    Mar 21, 2009
    Posts:
    17
    I tried a few variants of the Server portion of the connection string, with the following results.

    "Server=string.sqlexpress;"
    Connecting to the database(Server=string.sqlexpress;Database=AmksPhaseII;User ID=string\barry;Password=ttpp1357.;Integrated Security=SSPI)
    Exception: Server does not exist or connection refused.​
    "Server=string;"
    Connecting to the database(Server=string;Database=AmksPhaseII;User ID=string\barry;Password=ttpp1357.;Integrated Security=SSPI)
    Exception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.​
    "Server=string\\sqlexpress;"
    Connecting to the database(Server=string\sqlexpress;Database=AmksPhaseII;User ID=string\barry;Password=ttpp1357.;Integrated Security=SSPI)
    Exception: An existing connection was forcibly closed by the remote host.

     
  3. Barry Holroyd

    Barry Holroyd

    Joined:
    Mar 21, 2009
    Posts:
    17
    I followed jashan's suggestions at http://forum.unity3d.com/threads/9391-MS-SQL-connection?highlight=database+close+connection. Specifically:

    I turned on TCP (it was off, but I'm not sure it was using TCP/IP since everything was local and my Mono standalone app. worked fine). That didn't make any difference.

    However, I'm not 100% sure I have the right System.Data file. I could find two possible files, both in Unity3.1/..., but given jashan's comments that I should use the *Unity* version and not the *Mono* version, which should I choose?

    Unity3.1 (mono 2.0): c:/Program Files (x86)/Unity3.1/Editor/Data/Mono/lib/mono/2.0/System.Data.dll
    Unity3.1 (unity): c:/Program Files (x86)/Unity3.1/Editor/Data/Mono/lib/mono/unity/System.Data.dll
    They are both in the unity hierarchy.

    I was using the "mono 2.0" version, which fails to connect (as above).

    When I switched to the "unity" version, it fails to compile, with the following error message.

    c:\Data\career\Green Light Immersion\Heartwood\clients\Raytheon\AMKS\phase II\Unity\Amks\Assets\Scripts\AmksTester.cs(7,7): Error CS0012: The type 'System.ComponentModel.Component' is defined in an assembly that is not referenced. You must add a reference to assembly 'System, Version=2.0.5.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e'. (CS0012) (Amks)​

    I suspect that this is because of the following version numbers involved.

    System.dll: Version 2.0.0.0
    System.Data.dll: Version 2.0.5.0

    I.e., I suspect that it doesn't let a newer version depend on an older version, at least not in this case (although I'm not an assembly expert, so I could be wrong).

    In any case, what do I do???

    Thanks,
    Barry
     
  4. Barry Holroyd

    Barry Holroyd

    Joined:
    Mar 21, 2009
    Posts:
    17
    One more very important thing. I turned on auditing on my SQL Express server. When I connect with my Mono App, it (unsurprisingly) shows the user successfully logging in.

    However, when I attempt to do the exact same thing from Unity, the request never appears to reach the server -- i.e., no login attempt is recorded in the error/event log (and yes, it is configured to show both success and failures :)).

    So it doesn't appear to be a problem with the connection string, unless it's the Server portion.

    How can I get this to at least reach the server with the request? Is there some network setting I need to configure into Unity to allow it to communicate over TCP/IP to this particular application (SQL Server)?

    BTW, turning off my firewall didn't make any difference either.

    Thanks,
    Barry
     
  5. Barry Holroyd

    Barry Holroyd

    Joined:
    Mar 21, 2009
    Posts:
    17
  6. Barry Holroyd

    Barry Holroyd

    Joined:
    Mar 21, 2009
    Posts:
    17
  7. Darkfury

    Darkfury

    Joined:
    May 27, 2011
    Posts:
    4
    Just stumbled across this and thought perhaps you might like a response at least, though I fear it might not be terribly helpful.

    First up, I know next to nothing about Unity. With that out of the way though;

    Looking at your connection string, do you have "string" defined somewhere that resolves to the local server name?

    If the server you are trying to connect to is local (I think you said it was) I would try connecting to a server name of .\sqlexpress or just as a test (its a bit of a bodge) amend your TCP hostfile to include a line at the bottom reading "127.0.0.1 string" without the quotes, or replace the 127.0.0.1 address with the real IP address if the database server is in fact remote.

    The location of the hostfile depends on OS and sometimes on version, but if you are on Windows have a look in c:\windows\system32\drivers\etc.
     
  8. Barry Holroyd

    Barry Holroyd

    Joined:
    Mar 21, 2009
    Posts:
    17
    Darkfury,

    Thank you for the reply!

    I don't think that is the problem, however. Note that the exact same code, running in a standalone application, can resolve the hostname "string" just fine (that is, in fact, the name of my local system -- this is all running on my laptop... nothing is remote). Moreover, using Tcpview I don't even see any *attempt* at opening a connection and Jashan, in other postings, says that using IP addresses (as opposed to DNS hostnames) is to be avoided. Having said all that, I may still give it a shot Monday, when I get the chance.

    Thank you! I appreciate you taking the time to help me out.

    Barry
     
  9. EAM

    EAM

    Joined:
    Jan 1, 2013
    Posts:
    1
    Hi Barry,

    Were you ever able to resolve this issue. I'm having trouble connecting too. I am able to connect to my sqlexpress from a different windows machine but not from my Ubuntu/Unity. Thanks
     
  10. Joe rco

    Joe rco

    Joined:
    Jul 17, 2013
    Posts:
    1
    Use just the host name and port .ie. 127.0.0.1,1433