integrated security

• 
  In what way does the connection fail? Is there some sort of error message?

  Brian Donahue October 12, 2007 09:08

  - 0 +
• 
  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.

  SimonMartin October 12, 2007 10:32

  - 0 +
• 
  Do you need to connect to the Express server as a particular user (a user other than the one you have logged in as)?

  Brian Donahue October 12, 2007 12:17

  - 0 +
• 
  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?

  SimonMartin October 15, 2007 17:53

  - 0 +
• 
  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?

  Brian Donahue October 16, 2007 06:13

  - 0 +
• 
  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.

  SimonMartin October 16, 2007 16:34

  - 0 +
• 
  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?

  Brian Donahue October 17, 2007 10:20

  - 0 +
• 
  I don't have Management Studio - does it come with Visual Studio?

  SimonMartin October 18, 2007 13:29

  - 0 +
• 
  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

  SimonMartin October 18, 2007 14:31

  - 0 +
• 
  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.

  Brian Donahue October 19, 2007 06:42

  - 0 +
• 
  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
  
  Simon

  SimonMartin October 23, 2007 17:01

  - 0 +

Add comment

Please sign in to leave a comment.