Configuring SQL Express -- getting login failed exception

I have a machine with Windows XP Pro w/SP 2, IIS, SQL Server Express and Visual Studio 2005 installed. When I attempt to open a database from an ASPX page, I'm getting an exception with the message:

"Cannot open database "Tasks" requested by the login. The login failed.
Login failed for user 'MYDEV\ASPNET'."

I've searched the web and found a number of pages that talk about the "Login failed for MACHINE\ASPNET" issue, but I didn't find any with a solution.

For your information (in case it makes any difference), the computer is connected to a domain. And the authentication methods for the virtual directory (in IIS) is configured for "Anonymous access" and "Integrated Windows authentication."

Does anyone know how to configure SQL Server Express (or is it IIS?) so the web page can access the database?

RichardR

Okay, I found a solution.

For anyone else experiencing this problem, here's what I did:

First, I installed the November CTP of SQL Server Management Studio Express.

Then, I added the account MACHINENAME\ASPNET under Security | Logins. I did a right-mouse click on the ASPNET login name and clicked on Properties.

This brought up the Login Properties page for ASPNET. I mapped the ASPNET account to my database selecting the database in the "Default Database" dropdown.

In order for the stored procedures to execute, I also needed to check the db_owner box under the Mappings tab.

Finally, I needed to change the connection string to:

Data Source=MYDEV\SQLEXPRESS;Connect Timeout=30;User Instance=False;initial catalog=Tasks;Integrated Security=SSPI;Persist Security Info=False

Hope this helps anyone else encountering this problem.

Richard

|||Thanks - that was very helpful!!!
|||Thanks for the post! Saved my life after hours of searching!!!

Configuring SQL Express -- getting login failed exception

I have a machine with Windows XP Pro w/SP 2, IIS, SQL Server Express and Visual Studio 2005 installed. When I attempt to open a database from an ASPX page, I'm getting an exception with the message:

"Cannot open database "Tasks" requested by the login. The login failed.
Login failed for user 'MYDEV\ASPNET'."

I've searched the web and found a number of pages that talk about the "Login failed for MACHINE\ASPNET" issue, but I didn't find any with a solution.

For your information (in case it makes any difference), the computer is connected to a domain. And the authentication methods for the virtual directory (in IIS) is configured for "Anonymous access" and "Integrated Windows authentication."

Does anyone know how to configure SQL Server Express (or is it IIS?) so the web page can access the database?

RichardR

Okay, I found a solution.

For anyone else experiencing this problem, here's what I did:

First, I installed the November CTP of SQL Server Management Studio Express.

Then, I added the account MACHINENAME\ASPNET under Security | Logins. I did a right-mouse click on the ASPNET login name and clicked on Properties.

This brought up the Login Properties page for ASPNET. I mapped the ASPNET account to my database selecting the database in the "Default Database" dropdown.

In order for the stored procedures to execute, I also needed to check the db_owner box under the Mappings tab.

Finally, I needed to change the connection string to:

Data Source=MYDEV\SQLEXPRESS;Connect Timeout=30;User Instance=False;initial catalog=Tasks;Integrated Security=SSPI;Persist Security Info=False

Hope this helps anyone else encountering this problem.

Richard

|||Thanks - that was very helpful!!!
|||Thanks for the post! Saved my life after hours of searching!!!

Configuring SQL Express -- getting login failed exception

I have a machine with Windows XP Pro w/SP 2, IIS, SQL Server Express and Visual Studio 2005 installed. When I attempt to open a database from an ASPX page, I'm getting an exception with the message:

"Cannot open database "Tasks" requested by the login. The login failed.
Login failed for user 'MYDEV\ASPNET'."

I've searched the web and found a number of pages that talk about the "Login failed for MACHINE\ASPNET" issue, but I didn't find any with a solution.

For your information (in case it makes any difference), the computer is connected to a domain. And the authentication methods for the virtual directory (in IIS) is configured for "Anonymous access" and "Integrated Windows authentication."

Does anyone know how to configure SQL Server Express (or is it IIS?) so the web page can access the database?

RichardR

Okay, I found a solution.

For anyone else experiencing this problem, here's what I did:

First, I installed the November CTP of SQL Server Management Studio Express.

Then, I added the account MACHINENAME\ASPNET under Security | Logins. I did a right-mouse click on the ASPNET login name and clicked on Properties.

This brought up the Login Properties page for ASPNET. I mapped the ASPNET account to my database selecting the database in the "Default Database" dropdown.

In order for the stored procedures to execute, I also needed to check the db_owner box under the Mappings tab.

Finally, I needed to change the connection string to:

Data Source=MYDEV\SQLEXPRESS;Connect Timeout=30;User Instance=False;initial catalog=Tasks;Integrated Security=SSPI;Persist Security Info=False

Hope this helps anyone else encountering this problem.

Richard

|||Thanks - that was very helpful!!!|||Thanks for the post! Saved my life after hours of searching!!!

Configuring Reporting Services behind a firewall

Hi,

I put my SQL server behind the firewall, and I have put SQL_PORT and “Reporting Services Configuration” in the exception list.

However, on a remote machine, if I use SQL Reporting Services Configuration Manager to change the Database Connection setting, I will get an error in the “Create a Grants Rights Script” stage.

Looking at the exception, it points to an RPC problem.

System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)

at System.Management.ManagementScope.InitializeGuts(Object o)

at System.Management.ManagementScope.Initialize()

at System.Management.ManagementObjectSearcher.Initialize()

at System.Management.ManagementObjectSearcher.Get()

at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses(String machine)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String dbServer)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)

Does it mean that I have to enable ALL RPC ports in order to configure RS behind a firewall?

Thanks,

Patrick Ng

Did Anybody found a solution for this..

I'm also facing the same problem...

Thanks

|||

The Configuration Manager uses WMI in some communication with the SQL Server catalog machine. You will need to ensure your firewall allows DCOM traffic between the two boxes while you are setting them up. The following article suggests protecting communication between the boxes using IPsec:

http://msdn2.microsoft.com/en-us/library/ms159272.aspx

Thanks, Donovan.

|||

Just to clarify - In the firewalled scenario, typically you are trying to allow users from outside the firewall to view/access reports. To do this they need URL based access (typically port 80) on to the report server. These users do not need acess to WMI - it is only used for administrative reasons (like creating the report server database). You should not expose the WMI provider (DCOM) outside your firewall. The operation you’re trying to do is an administrative operation that should be carried out inside the safety of your network either locally on the computer or through a secure channel inside your network. Exposing DCOM to the broader network significantly increases your security exposure and should not be done unless you have really good reason for doing it and consider ahead of time the implications.

Hope that helps,

-Lukasz

|||From what I could tell DCOM runs on TCP 135. When I opened this port between my reporting server and my sql server, it still gives the same error. Are there additional ports that you're aware of, or am I wrong altogether?|||Open your firewall log and see if you can determine which port is being used but is blocked.

Configuring Reporting Services behind a firewall

Hi,

I put my SQL server behind the firewall, and I have put SQL_PORT and “Reporting Services Configuration” in the exception list.

However, on a remote machine, if I use SQL Reporting Services Configuration Manager to change the Database Connection setting, I will get an error in the “Create a Grants Rights Script” stage.

Looking at the exception, it points to an RPC problem.

System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)

at System.Management.ManagementScope.InitializeGuts(Object o)

at System.Management.ManagementScope.Initialize()

at System.Management.ManagementObjectSearcher.Initialize()

at System.Management.ManagementObjectSearcher.Get()

at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses(String machine)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String dbServer)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)

Does it mean that I have to enable ALL RPC ports in order to configure RS behind a firewall?

Thanks,

Patrick Ng

Did Anybody found a solution for this..

I'm also facing the same problem...

Thanks

|||

The Configuration Manager uses WMI in some communication with the SQL Server catalog machine. You will need to ensure your firewall allows DCOM traffic between the two boxes while you are setting them up. The following article suggests protecting communication between the boxes using IPsec:

http://msdn2.microsoft.com/en-us/library/ms159272.aspx

Thanks, Donovan.

|||

Just to clarify - In the firewalled scenario, typically you are trying to allow users from outside the firewall to view/access reports. To do this they need URL based access (typically port 80) on to the report server. These users do not need acess to WMI - it is only used for administrative reasons (like creating the report server database). You should not expose the WMI provider (DCOM) outside your firewall. The operation you’re trying to do is an administrative operation that should be carried out inside the safety of your network either locally on the computer or through a secure channel inside your network. Exposing DCOM to the broader network significantly increases your security exposure and should not be done unless you have really good reason for doing it and consider ahead of time the implications.

Hope that helps,

-Lukasz

|||From what I could tell DCOM runs on TCP 135. When I opened this port between my reporting server and my sql server, it still gives the same error. Are there additional ports that you're aware of, or am I wrong altogether?
|||Open your firewall log and see if you can determine which port is being used but is blocked.

Configuring Reporting Services behind a firewall

Hi,

I put my SQL server behind the firewall, and I have put SQL_PORT and “Reporting Services Configuration” in the exception list.

However, on a remote machine, if I use SQL Reporting Services Configuration Manager to change the Database Connection setting, I will get an error in the “Create a Grants Rights Script” stage.

Looking at the exception, it points to an RPC problem.

System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)

at System.Management.ManagementScope.InitializeGuts(Object o)

at System.Management.ManagementScope.Initialize()

at System.Management.ManagementObjectSearcher.Initialize()

at System.Management.ManagementObjectSearcher.Get()

at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses(String machine)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String dbServer)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)

Does it mean that I have to enable ALL RPC ports in order to configure RS behind a firewall?

Thanks,

Patrick Ng

Did Anybody found a solution for this..

I'm also facing the same problem...

Thanks

|||

The Configuration Manager uses WMI in some communication with the SQL Server catalog machine. You will need to ensure your firewall allows DCOM traffic between the two boxes while you are setting them up. The following article suggests protecting communication between the boxes using IPsec:

http://msdn2.microsoft.com/en-us/library/ms159272.aspx

Thanks, Donovan.

|||

Just to clarify - In the firewalled scenario, typically you are trying to allow users from outside the firewall to view/access reports. To do this they need URL based access (typically port 80) on to the report server. These users do not need acess to WMI - it is only used for administrative reasons (like creating the report server database). You should not expose the WMI provider (DCOM) outside your firewall. The operation you’re trying to do is an administrative operation that should be carried out inside the safety of your network either locally on the computer or through a secure channel inside your network. Exposing DCOM to the broader network significantly increases your security exposure and should not be done unless you have really good reason for doing it and consider ahead of time the implications.

Hope that helps,

-Lukasz

|||From what I could tell DCOM runs on TCP 135. When I opened this port between my reporting server and my sql server, it still gives the same error. Are there additional ports that you're aware of, or am I wrong altogether?
|||Open your firewall log and see if you can determine which port is being used but is blocked.

Configuring Reporting Services behind a firewall

Hi,

I put my SQL server behind the firewall, and I have put SQL_PORT and “Reporting Services Configuration” in the exception list.

However, on a remote machine, if I use SQL Reporting Services Configuration Manager to change the Database Connection setting, I will get an error in the “Create a Grants Rights Script” stage.

Looking at the exception, it points to an RPC problem.

System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)

at System.Management.ManagementScope.InitializeGuts(Object o)

at System.Management.ManagementScope.Initialize()

at System.Management.ManagementObjectSearcher.Initialize()

at System.Management.ManagementObjectSearcher.Get()

at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses(String machine)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String dbServer)

at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)

Does it mean that I have to enable ALL RPC ports in order to configure RS behind a firewall?

Thanks,

Patrick Ng

Did Anybody found a solution for this..

I'm also facing the same problem...

Thanks

|||

The Configuration Manager uses WMI in some communication with the SQL Server catalog machine. You will need to ensure your firewall allows DCOM traffic between the two boxes while you are setting them up. The following article suggests protecting communication between the boxes using IPsec:

http://msdn2.microsoft.com/en-us/library/ms159272.aspx

Thanks, Donovan.

|||

Just to clarify - In the firewalled scenario, typically you are trying to allow users from outside the firewall to view/access reports. To do this they need URL based access (typically port 80) on to the report server. These users do not need acess to WMI - it is only used for administrative reasons (like creating the report server database). You should not expose the WMI provider (DCOM) outside your firewall. The operation you’re trying to do is an administrative operation that should be carried out inside the safety of your network either locally on the computer or through a secure channel inside your network. Exposing DCOM to the broader network significantly increases your security exposure and should not be done unless you have really good reason for doing it and consider ahead of time the implications.

Hope that helps,

-Lukasz

|||From what I could tell DCOM runs on TCP 135. When I opened this port between my reporting server and my sql server, it still gives the same error. Are there additional ports that you're aware of, or am I wrong altogether?|||Open your firewall log and see if you can determine which port is being used but is blocked.

configure SQL Server Express with Windows Firewall

I have changed SQL Server port to 2433, and add it to exception in Windows Firewall, add executive files as in this KB http://msdn2.microsoft.com/en-us/library/ms175043.aspx

Want to change port of SQL Server Browser as well, but dont know how to :(

anyway, after enable Firewall, SQL server is stop working. How to get it working with Firewall? Also, if some one lets me know how to change port of SQL Server Browser too, it would be great

thanks

As far as I know, you can not change the port that is used by SQL Browser. Based on this BOL topic, it seem that port has to remain the same.

I'd need more information to answer the other question. What do you mean that SQL Server stops working? If you've changed the port that SQL is using, you will need to specify the exact port number in your connection, are you doing that?

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

|||

You cannot change the UDP port number (1434) for SQL browser. The clients will always connect to this port to obtain SQL Server configuration.

In your case, you need to put UDP 1434 or SQL Browser into your firewall exception.