locked
Cannot connect to Local Instance of SQL using Windows Authentication with TCP/IP RRS feed

  • Question

  • Hi,

     

    I am having trouble connecting to my local instance of SQL 2005 with TCP/IP / Windows Authentication

     

    I am able to connect using the sa account and also using windows authentication if I use Shared Memory or Named pipes as the Network Protocol. Everytime I try and authenticate using the Servername as the .\ notation with Windows Authentication and TCP/IP as the Network Protocol it gives the error

    The User is not associated with a trusted SQL Server connection

    TCP IP is enabled in SQL Server Configuration Manager

    I have looked in the logs and SQL Sever is listening on Port 1800

    Finally and probably most interestingly if use the following notation to connect it works

    127.0.0.1\SQL_2005

     

    I am at a loss, if anyone could help that would be much appreciated

     

    Thanks

     

    James

    Wednesday, September 7, 2011 7:50 AM

Answers

  • Hi Uwe,

     

    Thanks for your replies, digging a little deeper into the problem I checked the security logs and I don't have the required permissions to do this. Need to get onto the techies to get them to change some policies

     

    Regards

     

    James

     

    • Marked as answer by Stephanie Lv Thursday, September 15, 2011 12:18 PM
    Wednesday, September 7, 2011 10:30 AM

All replies

  • Hallo James,

    what account did you add as Login to the sql instance?
    Is it the account you've logged in when you start your machine?

    Do you login from the same machine?
    Do you specify the port when you enter the name of the machine?
    (e.g. Machine\Instance 1800)

    Is SQLBrowser service up and running?


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Wednesday, September 7, 2011 8:11 AM
  • Hi Uwe,

     

    I use my windows account so Domain\User which is added as a member of the sysadmin role

    I log on from the same machine 

    I don't specify the port when i enter the name of the machine

    My SQL Browser service is up and running

    Regards

    James

     

     

    Wednesday, September 7, 2011 8:56 AM
  • Hallo James,

    can you please try the following:

    - disable the local firewall on your windows machine
    - try to connect

    (local) resolves to 127.0.0.1, and works for defaults only. Instances, like the default for SQL Server Express, need a real name. Note that you can check the error log for SQL Server (SQL install folder, then log) and one of the first few entries will give you the IP, port, and name of the instance.

    Any more information from the Error Log of SQL Server?

    Does it work?


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Wednesday, September 7, 2011 9:46 AM
  • Hallo James,

    Another option is to connect specific with the tcp prefix...

    tcp:.\InstanceName
    Does this work?

    I'm wondering why you want to connect to local server with TCP/IP.
    Basically local connections are using shared memory...


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Wednesday, September 7, 2011 9:53 AM
  • Hi Uwe,

     

    Thanks for your replies, digging a little deeper into the problem I checked the security logs and I don't have the required permissions to do this. Need to get onto the techies to get them to change some policies

     

    Regards

     

    James

     

    • Marked as answer by Stephanie Lv Thursday, September 15, 2011 12:18 PM
    Wednesday, September 7, 2011 10:30 AM