1

I am giving up. I have setup my database with user tcpisanweb and gave access as the db owner of the database. In my web.config 

<add name="TCPISANConnectionString" connectionString="Data Source=PHOENIX\DMZSQL;Initial Catalog=TCPISAN;uid=HCDIDMZ\tcpisanweb;pwd=1tcpisan@web"
  providerName="System.Data.SqlClient" />

My SQL setting for the database is below: enter image description here

Also, my security login screenshot is here enter image description here

and i still get the error "Login failed for user 'HCDIDMZ\tcpisanweb'." enter image description here

Also, in my SQL Configuration manager, I have setup my TCP/IP enabled and assigned the TCP port to 1433 under the IP Addresses tab.

I am not sure what else to do, to connect successfully.

Ron
  • 1,901
  • 4
  • 19
  • 38
  • 2
    Have you enabled [mixed mode authentication](https://msdn.microsoft.com/en-us/library/ms144284.aspx)? Also change your password now you've posted it online. – Martin Smith Feb 05 '16 at 20:44
  • that's a 'madeup' password :) not to worry. Thanks. – Ron Feb 05 '16 at 20:48

2 Answers2

2

If you look in the SQL Server Error log it will have more details about the reason for failure.

I notice that you are using SQL Authentication. Out of the box this is disabled and needs to be enabled - e.g. by using the Security tab in the Server Properties dialogue.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • I just checked and it is set to "SQL Server and Windows Authentication mode" – Ron Feb 05 '16 at 20:57
  • 1
    So look in the log then. Under Management -> SQL Server Logs in SSMS – Martin Smith Feb 05 '16 at 20:57
  • I just checked the sql server logs and it says "Logon,Unknown,Login failed for user 'HCDIDMZ\tcpisanweb'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: ] – Ron Feb 05 '16 at 21:01
  • 1
    I should have spotted that. You need to create a SQL Server Login if you are using SQL Server authentication and passing username and password. For a windows login you don't provide any user name or password at all in the connection string. It will use the account the app pool is running under. – Martin Smith Feb 05 '16 at 21:04
  • when i change it to windows authentication and removed the credentials in the web.config and added integrated security=True attribute, i am getting "Login failed for user 'IIS APPPOOL\TCPISAN'. error now. – Ron Feb 05 '16 at 21:08
  • 1
    @Ron - So you need to configure asp.net to use your desired account. https://msdn.microsoft.com/en-us/library/bsz5788z.aspx – Martin Smith Feb 05 '16 at 21:11
  • Thanks Martin, will give it a try – Ron Feb 05 '16 at 21:17
  • Thanks for all the help Martin, after adding the IISAPPPOOL\TCPISAN user in the security\login tab and giving ownership to the db, I was able to connect to the database, without any issues. Also, this article was helpful - http://stackoverflow.com/questions/16427300/login-failed-for-user-iis-apppool-myapppool – Ron Feb 08 '16 at 16:46
0

Have you ensured that the TCP/IP protocol is enabled via Sql Server Configuration Manager.

To enable the TCP/IP network protocol

In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration.
In the console pane, click Protocols for .
In the details pane, right-click TCP/IP, and then click Enable.
In the console pane, click SQL Server Services.
In the details pane, right-click SQL Server (), and then click Restart, to stop and restart the SQL Server service.

Kevin
  • 7,162
  • 11
  • 46
  • 70