none
sqlConnection ..what should I expect ?? RRS feed

  • Question

  • Hi all,

     I'm trying to connect to my SQL SERVER 2000 database via Sqlconnection command with the code below,do I need to do something else to make sure the database is really opened ..I keep getting error at

         conn.Open();  which I dont know what do about ..

    any help will be really appreciated

     

       using (SqlConnection conn = new SqlConnection("Server=" + dbServer+";Database="+dbDatabase+";"))

        {

          conn.Open();

          SqlCommand cmd = conn.CreateCommand();

          cmd.CommandText = "SELECT * FROM LOCATIONS";

          cmd.ExecuteNonQuery();

          conn.Close();

        }

     

     

    thanks

    Alaa M

    Friday, August 18, 2006 6:38 PM

Answers

  • Hi Alaa,

    Are you sure that you've entered the server name/instance correctly?  Another possibility is that the firewall on the server is blocking named pipes traffic.  You need to allow the file and print sharing exception in the windows firewall.

    Alternatively, you can try using TCP/IP instead of named pipes.  First, make sure that TCP/IP is enabled on the server.  You can force the client to use TCP/IP for the connection by prefixing the server name with "tcp:".  Here are some articles that give informatoin for configuring the firewall for tcp/ip traffic:

    http://support.microsoft.com/kb/287932/en-us
    http://support.microsoft.com/kb/841249/en-us

    Il-Sung.

    Tuesday, August 22, 2006 9:24 PM

All replies

  • You need to add something to your connection string. Either supply a username and password or indicate that integrated security should be used.

    "server=myServer;user id=aUsr;password=secret;Database=myDb"

    "server=myServer;integrated security=true;Database=myDb"

     

    Friday, August 18, 2006 7:44 PM
  •  Why do I need to include an userid or password if I'm using windows authintication ? unless I'm missing something here !

    Do I need to do something from the SQL SERVER side ? or it shoulfd be only in the VS's side ?

      Thanks for your help I really appreciat it

       Alaa M

     

     

    Sunday, August 20, 2006 2:35 PM
  • If you are using integrated security you need to indicate that in the connection string as well. That syntax was included above too.
    Sunday, August 20, 2006 3:02 PM
  •  Hi again,

    actually ,I added the security in my statement ,which is now looks like this

    using (SqlConnection conn = new SqlConnection("server=" + dbServer + ";database=" + dbDatabase + ";Integrated Security=true"))

    but I still keep getting the error on the conn.Open();

    which is saying

    "An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

    I did cheked the settings in SQL SERVER and the remote connection option is enabled,so,what do you think else I need to do in order to acheive my connection ?

     

    Thanks again for the help

    Alaa M

    Monday, August 21, 2006 12:59 PM
    •  SQL Native Client ODBC Driver

      •  Standard security:
        "Driver={SQL Native Client};Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;"


      •  Trusted connection:
        "Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
        Equivalents
        Integrated Security=SSPI equals Trusted_Connection=yes
      •  Prompt for username and password:
        oConn.Properties("Prompt") = adPromptAlways
        oConn.Open "Driver={SQL Native Client};Server=Aron1;DataBase=pubs;"


      •  Enabling MARS (multiple active result sets):
        "Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;MARS_Connection=yes"
        Equivalents
        MultipleActiveResultSets=true equals MARS_Connection=yes

        Using MARS with SQL Native Client, by Chris Lee >>
      •  Encrypt data sent over network:
        "Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;Encrypt=yes"

        Pls click the following link
      • http://www.codeproject.com/cs/database/sql_in_csharp.asp

       

    Tuesday, August 22, 2006 7:39 AM
  • Hi Alaa,

    Are you sure that you've entered the server name/instance correctly?  Another possibility is that the firewall on the server is blocking named pipes traffic.  You need to allow the file and print sharing exception in the windows firewall.

    Alternatively, you can try using TCP/IP instead of named pipes.  First, make sure that TCP/IP is enabled on the server.  You can force the client to use TCP/IP for the connection by prefixing the server name with "tcp:".  Here are some articles that give informatoin for configuring the firewall for tcp/ip traffic:

    http://support.microsoft.com/kb/287932/en-us
    http://support.microsoft.com/kb/841249/en-us

    Il-Sung.

    Tuesday, August 22, 2006 9:24 PM