none
connecting to the Sql server 2008 using 127.0.0.1 RRS feed

  • Question

  • hi all, 

    I want to test a code written C# which communicate with sql server 2008 . both the client and server reside in the same computer. I could establish communication using the following connection string;

         string Connection = "Server=localhost;Data Source=IQBAL-PC\\SQLEXPRESS;Database=MyDataBase1;Integrated Security=SSPI";

    but my intention is that I want to ensure , in a LAN connected client server model the code should work properly . But  I have only one PC at my home . so I want to use the same computer as both client and server . hence I would like to simulate a model where in my client program uses the loop back port 127.0.0.1 . So, to certain extent I can hope that my code will establish connection to a remote computer which connected in a LAN, if the following connection string works!

    string Connection = "Data Source=127.0.0.1,1433;Network Library=DBMSSOCN;Initial Catalog=MyDataBase1;User ID=sa;Password=3151980";

    in TCP/IP properties of sql service configuration manager I made all IP TCP port 1433 (IP1 to IP 9 and IPALL)


    I am giving some screen shots

    through sql server management studio Remote connection also enabled 

    but unfortunately it gives the error 

    System.Windows.Markup.XamlParseException was unhandled
      Message='The invocation of the constructor on type 'sqltest.MainWindow' that matches the specified binding constraints threw an exception.' Line number '3' and line position '9'.
      Source=PresentationFramework
      LineNumber=3
      LinePosition=9
      StackTrace:
           at System.Windows.Markup.XamlReader.RewrapException(Exception e, IXamlLineInfo lineInfo, Uri baseUri)
           at System.Windows.Markup.WpfXamlLoader.Load(XamlReader xamlReader, IXamlObjectWriterFactory writerFactory, Boolean skipJournaledProperties, Object rootObject, XamlObjectWriterSettings settings, Uri baseUri)
           at System.Windows.Markup.WpfXamlLoader.LoadBaml(XamlReader xamlReader, Boolean skipJournaledProperties, Object rootObject, XamlAccessLevel accessLevel, Uri baseUri)
           at System.Windows.Markup.XamlReader.LoadBaml(Stream stream, ParserContext parserContext, Object parent, Boolean closeStream)
           at System.Windows.Application.LoadBamlStreamWithSyncInfo(Stream stream, ParserContext pc)
           at System.Windows.Application.LoadComponent(Uri resourceLocator, Boolean bSkipJournaledProperties)
           at System.Windows.Application.DoStartup()
           at System.Windows.Application.<.ctor>b__1(Object unused)
           at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
           at MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
           at System.Windows.Threading.DispatcherOperation.InvokeImpl()
           at System.Windows.Threading.DispatcherOperation.InvokeInSecurityContext(Object state)
           at System.Threading.ExecutionContext.runTryCode(Object userData)
           at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
           at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Windows.Threading.DispatcherOperation.Invoke()
           at System.Windows.Threading.Dispatcher.ProcessQueue()
           at System.Windows.Threading.Dispatcher.WndProcHook(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
           at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
           at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
           at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
           at MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
           at System.Windows.Threading.Dispatcher.InvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
           at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
           at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)
           at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
           at System.Windows.Threading.Dispatcher.PushFrame(DispatcherFrame frame)
           at System.Windows.Application.RunDispatcher(Object ignore)
           at System.Windows.Application.RunInternal(Window window)
           at System.Windows.Application.Run(Window window)
           at System.Windows.Application.Run()
           at sqltest.App.Main() in D:\Project\sqltest\sqltest\obj\x86\Debug\App.g.cs:line 0
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: System.Data.SqlClient.SqlException
           Message=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 - No connection could be made because the target machine actively refused it.)
           Source=.Net SqlClient Data Provider
           ErrorCode=-2146232060
           Class=20
           LineNumber=0
           Number=10061
           Server=""
           State=0
           StackTrace:
                at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
                at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
                at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity)
                at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)
                at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
                at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
                at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
                at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
                at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
                at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
                at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
                at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
                at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
                at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
                at System.Data.SqlClient.SqlConnection.Open()
                at sqltest.MainWindow..ctor() in D:\Project\sqltest\sqltest\MainWindow.xaml.cs:line 46
           InnerException: 

    thanks in advance 


    itismeiqbal


    • Edited by iqbal1980 Saturday, May 26, 2012 1:16 PM
    Saturday, May 26, 2012 1:09 PM

Answers

  • thanks for the reply . but it worked fine later . only think I had to restart the server

    Yes, a service restart is needed after changes to the network configuration.  Since the "127.0.0.1,1433" connection string worked, you must have changed the SQLEXPRESS named instance dynamic port to 1433 (assuming you don't have a default instance installed).


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/



    • Edited by Dan GuzmanMVP Saturday, May 26, 2012 4:59 PM
    • Marked as answer by iqbal1980 Sunday, June 3, 2012 1:17 PM
    Saturday, May 26, 2012 4:56 PM

All replies

  • string Connection = "Data Source=127.0.0.1,1433;Network Library=DBMSSOCN;Initial Catalog=MyDataBase1;User ID=sa;Password=3151980";

    Since you are using a named instance, the port is not 1433; it is a dynamically assigned port number.  If the SQL Browser service is running you can use the connection string below.  Make sure the SQL Browser port (UDP 1434) and the SQLEXPRESS named instance port are allowed through the firewall.

    string Connection = @"Data Source=127.0.0.1\SQLEXPRESS;Network Library=DBMSSOCN;Initial Catalog=MyDataBase1;User ID=sa;Password=3151980";


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Saturday, May 26, 2012 2:17 PM
  • thanks for the reply . but it worked fine later . only think I had to restart the server 


    itismeiqbal

    Saturday, May 26, 2012 3:54 PM
  • thanks for the reply . but it worked fine later . only think I had to restart the server

    Yes, a service restart is needed after changes to the network configuration.  Since the "127.0.0.1,1433" connection string worked, you must have changed the SQLEXPRESS named instance dynamic port to 1433 (assuming you don't have a default instance installed).


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/



    • Edited by Dan GuzmanMVP Saturday, May 26, 2012 4:59 PM
    • Marked as answer by iqbal1980 Sunday, June 3, 2012 1:17 PM
    Saturday, May 26, 2012 4:56 PM