none
SQL connectiviey Error RRS feed

  • Question

  • I have a server on which SQL 2008 R2 is installed and it has a datbase configured.

    I an trying to connect to database from different machine but getting following error:

    An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in ConsoleApplication1.exe

    Additional information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

    This is my connection string in app.config file

    <add name="ConnString" providerName="MySql.Data.SqlClient" connectionString="Server=serverIP,1500;Database=DatabaseName;Uid=Admin;Pwd=Password" />

    This is my .cs file

     string date = "17/7/2014";
                string hostname = "Computername";
                string userID = "UserID";
                string option = "Hibernate at 7 pm";
                
                try
                {
                    SqlConnection con = null;
                    System.Configuration.Configuration webconfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(null);
                    
                    if (webconfig.ConnectionStrings.ConnectionStrings != null)
                    {
                        string constr = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
                        con = new SqlConnection(constr);
                        con.Open();
                        SqlCommand cmd = new SqlCommand("dbo.ResponsesCaptured", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Date", date);
                        cmd.Parameters.AddWithValue("@Hostname", hostname);
                        cmd.Parameters.AddWithValue("@UserId", userID);
                        cmd.Parameters.AddWithValue("@OptionSelected", option);
                        cmd.ExecuteNonQuery();
                        Console.WriteLine(" Values Added to table ");
                        con.Close();
                    }
                }
                catch(SqlException ex)
                {
                    throw ex;
                }
                finally 
                {
                    Console.WriteLine("Could not open");
    
                }

    Please help.

    Thanks in advance




    \m/

    • Moved by KareninstructorMVP Monday, July 21, 2014 12:52 PM Moving from C# forum as this appears to be best placed in this forum
    Monday, July 21, 2014 4:08 AM

Answers

  • I found the solution.

    Seems like a silly mistake but I was trying windows credentials to connect to database using SQL authentication. Since the stste Error was showing 1 , I had to check event logs and found out the state to be 6 which occurs when you try windows credentials and SQL authentication.

    I created SQL account and it worked. 

    But when I tried connecting with SA it did not work .

    Any one have any idea why this happened?


    \m/


    Tuesday, July 22, 2014 3:44 AM

All replies

  • The problem should be in your connection string. Try putting a colon between the IP and port:

    <add name="ConnString" providerName="MySql.Data.SqlClient" connectionString="Server=serverIP:1500;Database=DatabaseName;Uid=Admin;Pwd=Password" />

    Monday, July 21, 2014 5:21 AM
  • Tried that also still not able to get connected.

    It says login failed for user Admin


    \m/

    Monday, July 21, 2014 9:05 AM
  • If it is a SQL Server 2008 R2 you should use:

    <add name="ConnString" providerName="System.Data.SqlClient" connectionString="Server=serverIP:1500;Database=DatabaseName;User Id=Admin;Password=Password" />
    Not sure if it matters though. Make sure you can login using the credentials you specify.

    Monday, July 21, 2014 9:32 AM
  • Hi OlofPetterson,

    This also did not work. Its been 4 days and this thing is not getting connected idk what is wrong.

    Till now I have read 100 of articles and tried 1000s of things to get it work but everytime I run the code it gives different error numbers!! IDK what to do :'(  :'(


    \m/

    Monday, July 21, 2014 10:47 AM
  • Try adding a connection through the wizard in the Server Explorer in Visual Studio. This way you will have the connection string generated for you and you will know it is correct.
    Monday, July 21, 2014 10:59 AM
  • Please refer to my post here:

    http://jqueryninja.wordpress.com/2011/01/10/how-do-i-get-the-connection-string/

     

    Noam B.


    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...

    Monday, July 21, 2014 1:48 PM
  • So far your Connection string is correct, it must be a comma to separate Server Name and IP port. Have you unblock the used port in the local Firewall?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, July 21, 2014 2:17 PM
  •  Hello,

    Please check/try by using below steps.

     

    1.On the SQL Server, open 'SQL Server Configuration Manager'. 

    2.Expand 'SQL Server Network Configuration' and highlight the 'Protocols for [Instance Name]' option.

    3.In the right-hand window, if 'TCP/IP' currently has the 'status' of 'Disabled', right click on 'TCP/IP' and select 'Enable'.

     it should be  ask restart the SQL Server service to complete the configuration change.

    4. Also ensure sql port should be open in windows firewall with in server.
    5.  Now check from sql instance connectivity from remotely

     

     

    Monday, July 21, 2014 2:37 PM
  • I found the solution.

    Seems like a silly mistake but I was trying windows credentials to connect to database using SQL authentication. Since the stste Error was showing 1 , I had to check event logs and found out the state to be 6 which occurs when you try windows credentials and SQL authentication.

    I created SQL account and it worked. 

    But when I tried connecting with SA it did not work .

    Any one have any idea why this happened?


    \m/


    Tuesday, July 22, 2014 3:44 AM