Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2003
    Posts
    4

    Unanswered: MSDE access denied error

    I set up MSDE that came with VS.NET and, using the Server Explorer, created a database called 'pubs' on my server. The MSSQLServer icon is lit up in the System Tray and has been started. When I created 'pubs', I was prompted whether I wanted to use NT Integrated Security or SQL Server Authentication. I was forced to choose the NT option because any login name that I used, including my administrator account, the 'aspnet_wp account', 'SQLDebugger' and 'sa'. Every time I tried to use the SQL Server Auth. option, I would get the following error prompt:

    ADO Error: '
    Login failed for user 'whateverItried'. Reason: Not associated with a trusted SQL Server connection.

    When running the following:

    Dim myConnection As SqlConnection = New SqlConnection("server=(local);database=pubs;uid=sa ;pwd=")

    it would result in this error message:

    Index #0
    Error: System.Data.SqlClient.SqlError: SQL Server does not exist or access denied.

    I also tried using 'localhost' and <my-computername>\<my-servername> for the server. For my database name, I tried both pubs and dbo.pubs. I also tried every username I could think of.

    No luck.

    Here's the funny thing: I was able to create a View on the database that did exactly what I was trying to do in code, so the DB is working.

    If anyone has sucessfully ran the first example program in Chapter 16 of Beginning VB.NET 2nd edition, please respond. That's where I got this example.

    Thanks for any help!

    Jason

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    What is the authentication mode used to connect?

    Refer to books online for Troubleshooting the Transact-SQL Debugger topic.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jun 2003
    Posts
    4
    Originally posted by Satya
    What is the authentication mode used to connect?

    Refer to books online for Troubleshooting the Transact-SQL Debugger topic.
    I'm not sure what you are asking. Remember that I'm using MSDE and I don't go through a lot of the setup that MS SQL uses.

    I was forced to use the NT Integrated Security, since no usernames or password combinations that I knew would work for the SQL option, if that's what you mean.

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    True, update the SQL Server Authentication mode back to SQL Server Authentification as referred in this KBA.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Jun 2003
    Posts
    4

    Agent

    I have only one icon running in my system tray and it is set to SQL Server, not SQL Server Agent.

    Do I need both running? If so, how can I get an instance of Agent to run as well as the server, as suggested in the KBA?

    By the way, thanks for your response!

    Jason

  6. #6
    Join Date
    Jun 2003
    Posts
    4

    Re: Agent

    Also, what username do I use? 'SA'?

    Jason

    Originally posted by jtimms
    I have only one icon running in my system tray and it is set to SQL Server, not SQL Server Agent.

    Do I need both running? If so, how can I get an instance of Agent to run as well as the server, as suggested in the KBA?

    By the way, thanks for your response!

    Jason

  7. #7
    Join Date
    Jul 2003
    Posts
    2

    Check to see if TCP\IP is enabled.

    We have the same issue. If you also have Enterprise Manager installed on the same machine, use it to be sure TCP/IP is an enabled protocol. We did that and were able to connect.

    The 64 dollar question is how you do this if you DON'T have Enterprise manager. Anyone know how to do it programatically?

    Jim M.
    Orrtax Software

  8. #8
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    Of course you can!

    Search for this file:

    svrnetcn.exe

    usually in:

    "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe"

    which is the Server Network Configuration
    Davide Mauri
    http://www.davidemauri.it

  9. #9
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    Manowar...thank you! I had the same problem as above, and it turned out that my server had not had TCP/IP enabled! Thank you so much!

  10. #10
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    3

    Smile Thanks!

    hi manomar - thanks a lot for your entry to this forum. i have had exactly the same problem and thru your entries - now it works!

    regards,


    Originally posted by manowar
    Of course you can!

    Search for this file:

    svrnetcn.exe

    usually in:

    "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe"

    which is the Server Network Configuration
    Last edited by dbBlue; 01-27-04 at 03:45.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    MSDE usually installs as a named instance, rather than the default instance (this way no two MSDE installs can interfere with each other...theoretically). Check in Administrative tools->services, and check the name of the MSSQL service. If it is MSSQLService, then it is amazingly the default instance. If it is MSSQL$blah, then you have a named instance. In order to address a named instance, you need to specify the name of the server, and the name of the instance as

    server=servername\blah

    You can also use IP addresses:

    10.10.10.10\blah

    Not sure if this works:

    (local)\blah

    Note that while the service is named with a '$', and all folders related to the instance are named with a '$', the service is addressed with '\'. Intuitive, right?

  12. #12
    Join Date
    Jan 2004
    Posts
    1
    I have another strange problem:

    When I use SVRNETCN or click on the properties in enterprise manager, I see that TCP/IP is enabled for my msde Instance.
    But when I look in the error.log of my instance, I see only the entry:
    SQL server listening on Named Pipes

    There is no entry that the server is listening on TCP/IP and I still get the error message "SQL Server does not exist or access denied" when I try to connect to my database over the network.
    So I think my server is still not listening on TCP/IP.

    Has anyone a workaround for this problem?

    Thanks in advance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •