integrated security

SimonMartinSimonMartin Posts: 12
edited October 23, 2007 5:01PM in SQL Prompt Previous Versions
I have sql prompt 3.6 and am trying to connect to my local Sql Express. The connection from Visual Studio to the SQL Express instance is using integrated security but there doesn't seem to be an option for that in the "Change connection" settings in SQL Prompt 3. Windows authentication fails, the only other option is a SQL connection, but I haven't created any logins in the SQL Express so I don't see that will work either.

So... how do I get it to connect?
Sharing knowledge saves valuable time!
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    In what way does the connection fail? Is there some sort of error message?
    · Share on Twitter
  • SimonMartinSimonMartin Posts: 12
    When it tries to authenticate a message appears saying
    "Authentication Failed"

    It isn't a failure of the product. Rather a failure to connect to the SQL Express database using Windows Authentication which I have chosen as there is no option for Integrated Security which is how my Windows form is connecting to the database.
    Sharing knowledge saves valuable time!
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Do you need to connect to the Express server as a particular user (a user other than the one you have logged in as)?
    · Share on Twitter
  • SimonMartinSimonMartin Posts: 12
    I haven't created any users for the SQL Express database.
    I created the database from Visual Studio and with the integrated security connection string I can see all my tables, sprocs etc.

    So would I need to create another user in order to authenticate with SQL Prompt, can't it also use the integrated security that Visual Studio is using?
    Sharing knowledge saves valuable time!
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Yes -- it should be using the Windows credentials of the logged-in user. Does it make a difference if you manually enter the server name as '127.0.0.1'? Maybe SQL Server thinks Prompt is a remote connection and remote connections are not allowed by your SQL Express installation?
    · Share on Twitter
  • SimonMartinSimonMartin Posts: 12
    It does make a difference when I use 127.0.0.1 as the server name (it takes longer to check); but unfortunately it still fails to authenticate. However when trying to browse for the .mdf file to connect to it throws up an error about remote connections not being allowed.

    There doesn't appear to be a "logins" node in Visual Studio for the Sql Express, so I don't think I can even add a user that way.

    Using my windows logon name (prefixed and not prefixed with machine name) and password does not authenticate either.
    Sharing knowledge saves valuable time!
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    No idea. How about using Management Studio to connect to the SQL Server Express instance, open the 'logs' folder, and see if there is an entry in the SQL Server log file at the time of connection failure specifying the reason why the connection attempt had failed?
    · Share on Twitter
  • SimonMartinSimonMartin Posts: 12
    I don't have Management Studio - does it come with Visual Studio?
    Sharing knowledge saves valuable time!
    · Share on Twitter
  • SimonMartinSimonMartin Posts: 12
    Ok - I've installed Management Studio (didn't realise there was a free version I've only used the full version for SQL Server)

    When using Management Studio I am able to get SQL Prompt to authenticate. But I can't then use Visual Studio to connect to the database. So after closing Management Studio and opening a Sproc in Visual Studio I am asked to authenticate for SQL Prompt, it fails to authenticate... and leaves nothing in either the logs (that I can see, my logs are totally empty) or in Event Viewer
    Sharing knowledge saves valuable time!
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    SQL Prompt interacts with Visual Studio by using the default server connection in your database project. If you want to change data sources, you can set a different connection as the default in the Solution Explorer or you can use the 'Change Connection' menu item in Prompt. Note that Integrated Security always uses your desktop login regardless: the only way you can specify the account to SQL Prompt is to specify a SQL login, which is apparently not allowed on your Express server.
    · Share on Twitter
  • SimonMartinSimonMartin Posts: 12
    Hi Brian,

    Thanks for sorting that out.
    Having installed Management Studio I can do everything I need (and its quicker at interacting with the database than Visual Studio) and I get SQL Prompt working as well - so I'm happy :D

    Simon
    Sharing knowledge saves valuable time!
    · Share on Twitter
Sign In or Register to comment.