Permissions

I posted a problem with connecting programmatically last week.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=590672&SiteID=1

Nobody seemed to have any suggestions, however since then, I've mucked things up and recovered to the same problem and now I've mucked things up again in a different way. Also last week I didn't mention that I was running my connection through remote desktop. That apparently can be a problem although not the fix.

I'm still getting the ServerVersion member status set to "'this.conn.ServerVersion' threw an exception of type 'System.InvalidOperationException' when I try to create a SqlConnection. I still don't understand why. But when I try to open the connection I now get the following exception

"Cannot open user default database. Login failed.\r\nLogin failed for user 'myserver\\ASPNET'."

which suggested permission problems. So using SQL Server Management Studio Express I opened my database and under properties selected "View server permissions" and the following message popped up.

TITLE: Microsoft SQL Server Management Studio Express

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Could not load file or assembly 'file:///C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlManagerUi.dll' or one of its dependencies. The system cannot find the file specified. (mscorlib)

There are no users listed under Permissions. I added guest permissions but it doesn't seem to make any difference. Since I'm running this under a web service, do I need to do something special?

Help

Cannot open user default database. Login failed.\r\nLogin failed for user 'myserver\\ASPNET'."

this error is trying to tell you there is an issue with the user's default database. ensure whatever account you are using to access your sqlserver has atleast public permissions.

In Mgmt. Studio/Object Explorer navigate to the Logins folder. Expand the folder and select the user you are attempting a client-side/ADO connection with. Select properties on the login and then select the "user mapping" tab. Check the public role for the user's assigned default database.

|||

Derek

Thanks for replying. Although not new to programmatically using databases I'm new to using sql server and not being familiar with it's terminology leaves me scratching my head while I try and hunt down what the error messages mean.

I've tried using both integrated security and adding my own user with a password. I know I'm probably missing something fundamental and thought maybe you had just given me the key. Unfortunately it doesn't appear to be the complete answer.

|||if you dont care about how you connect, for simplication purposes use sql authentication in your client code. create the account in sql server assign it's default database and ensure the sql account has atleast public (read) permission to that database.|||

Derek

I don't care at the moment how I connect. I would just like to see it connect.

I had already tried sql authentication before without success. After your response I tried again, messing with the connection string. No success.

|||is sql authentication enabled?|||

Derek

In SQL Server Management under Logins I've added a user called pubsub, set his login to SQL Server authentication and set his password. Then under User Mappings I made sure that public, reader, writer were checked.

In my application I used the following connection string,

"Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\WebServicesDemo\\PubSub\\App_Data\\PubSubDB.mdf;Database=PubSubDB;User ID=pubsub;Password=pspwd; Integrated Security=False;User Instance=True"

The connection error I get is

Login failed for user 'pubsub'. The user is not associated with a trusted SQL Server connection.|||you can run this code

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

GO

or simply enable sql authentication via the GUI/Mgmt. Studio. and restart the server after making the change.

|||

Derek

I found the place in the gui under Server properties where I was able to set Server authentication to SQL Server. I then restarted SQL Server. The response I now get from a connection attempt is

Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance. The connection will be closed.

Another interesting piece to this puzzle. My test setup involves a web application that makes a call to a web service. For simplification I moved the code to open the database to the web application using Windows authentication. It worked. I took the same code back to my web service and it doesn't work. What does that suggest? I tried that again with the current setup and received the same error above.

|||

Sorry, I hit the submit button to soon. I also meant to tell you that when I set Integrated Security=true, the connection in the web application works. When I set Integrated Security=true in the web service I get the following error

Unable to open the physical file \"C:\\WebServicesDemo\\PubSub\\App_Data\\PubSubDB.mdf\". Operating system error 5: \"5(Access is denied.)\".\r\nCould not attach file 'C:\\WebServicesDemo\\PubSub\\App_Data\\PubSubDB.mdf' as database 'PubSubDB'.

|||

Derek

Some more information. I noticed that although I've supposedly enabled SQL Server authentication (mixed mode), the connection properties Authentication method still show Windows Authentication. I tried to issue your registry change from sqlcmd and received

RegCreateKeyEx() returned error 5, 'Access is denied.'

So I created the key and value manually. It didn't change the connection property Authentication method which still showed Windows Authentication.

Everything I've try seems to work in the web application, but not in the Web Service. In the Web Service, I still received the "Unable to open the physical file" error.

|||

Derek

If your still following this I solved my problem. Although I knew it wouldn't be an acceptable final answer I decided as another data point to set impersonation in the web service web.config file using my personal login information. That worked, which confirmed to me the problem definitely had to do with the permissions of the web service account (I don't alway trust error messages).

So then I looked again at the account information to the database file itself. When I created the database using Visual Studio, VS put the database in the web services data directory and created a weird sqlexpress account (SQLServer2005MSSQLUser$WD49832$SQLEXPRESS (WD49832\SQLServer2005MSSQLUser$WD49832$SQLEXPRESS)) which I had just accepted managed VS's access to the database. Well I shouldn't trust MS to be so kind. I still don't know the purpose of that account but it obviously doesn't handle the application's access. Once I added the users group to the database file and gave them the necessary permissions it worked. I'm going to claim that there were just to many things I didn't understand in the beginning so I stepped into accepting bad assumptions. The upside I've learned more than I would had it been easy.

Thanks for your company and help while I struggled through that one.

|||your welcome and to be honest you taught me that "user instances" something I never use YET have to be used in conjunction with Integrated (windows) authentication. (which makes sense)

Permissions

I posted a problem with connecting programmatically last week.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=590672&SiteID=1

Nobody seemed to have any suggestions, however since then, I've mucked things up and recovered to the same problem and now I've mucked things up again in a different way. Also last week I didn't mention that I was running my connection through remote desktop. That apparently can be a problem although not the fix.

I'm still getting the ServerVersion member status set to "'this.conn.ServerVersion' threw an exception of type 'System.InvalidOperationException' when I try to create a SqlConnection. I still don't understand why. But when I try to open the connection I now get the following exception

"Cannot open user default database. Login failed.\r\nLogin failed for user 'myserver\\ASPNET'."

which suggested permission problems. So using SQL Server Management Studio Express I opened my database and under properties selected "View server permissions" and the following message popped up.

TITLE: Microsoft SQL Server Management Studio Express

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Could not load file or assembly 'file:///C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlManagerUi.dll' or one of its dependencies. The system cannot find the file specified. (mscorlib)

There are no users listed under Permissions. I added guest permissions but it doesn't seem to make any difference. Since I'm running this under a web service, do I need to do something special?

Help

Cannot open user default database. Login failed.\r\nLogin failed for user 'myserver\\ASPNET'."

this error is trying to tell you there is an issue with the user's default database. ensure whatever account you are using to access your sqlserver has atleast public permissions.

In Mgmt. Studio/Object Explorer navigate to the Logins folder. Expand the folder and select the user you are attempting a client-side/ADO connection with. Select properties on the login and then select the "user mapping" tab. Check the public role for the user's assigned default database.

|||

Derek

Thanks for replying. Although not new to programmatically using databases I'm new to using sql server and not being familiar with it's terminology leaves me scratching my head while I try and hunt down what the error messages mean.

I've tried using both integrated security and adding my own user with a password. I know I'm probably missing something fundamental and thought maybe you had just given me the key. Unfortunately it doesn't appear to be the complete answer.

|||if you dont care about how you connect, for simplication purposes use sql authentication in your client code. create the account in sql server assign it's default database and ensure the sql account has atleast public (read) permission to that database.|||

Derek

I don't care at the moment how I connect. I would just like to see it connect.

I had already tried sql authentication before without success. After your response I tried again, messing with the connection string. No success.

|||is sql authentication enabled?|||

Derek

In SQL Server Management under Logins I've added a user called pubsub, set his login to SQL Server authentication and set his password. Then under User Mappings I made sure that public, reader, writer were checked.

In my application I used the following connection string,

"Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\WebServicesDemo\\PubSub\\App_Data\\PubSubDB.mdf;Database=PubSubDB;User ID=pubsub;Password=pspwd; Integrated Security=False;User Instance=True"

The connection error I get is

Login failed for user 'pubsub'. The user is not associated with a trusted SQL Server connection.|||you can run this code

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

GO

or simply enable sql authentication via the GUI/Mgmt. Studio. and restart the server after making the change.

|||

Derek

I found the place in the gui under Server properties where I was able to set Server authentication to SQL Server. I then restarted SQL Server. The response I now get from a connection attempt is

Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance. The connection will be closed.

Another interesting piece to this puzzle. My test setup involves a web application that makes a call to a web service. For simplification I moved the code to open the database to the web application using Windows authentication. It worked. I took the same code back to my web service and it doesn't work. What does that suggest? I tried that again with the current setup and received the same error above.

|||

Sorry, I hit the submit button to soon. I also meant to tell you that when I set Integrated Security=true, the connection in the web application works. When I set Integrated Security=true in the web service I get the following error

Unable to open the physical file \"C:\\WebServicesDemo\\PubSub\\App_Data\\PubSubDB.mdf\". Operating system error 5: \"5(Access is denied.)\".\r\nCould not attach file 'C:\\WebServicesDemo\\PubSub\\App_Data\\PubSubDB.mdf' as database 'PubSubDB'.

|||

Derek

Some more information. I noticed that although I've supposedly enabled SQL Server authentication (mixed mode), the connection properties Authentication method still show Windows Authentication. I tried to issue your registry change from sqlcmd and received

RegCreateKeyEx() returned error 5, 'Access is denied.'

So I created the key and value manually. It didn't change the connection property Authentication method which still showed Windows Authentication.

Everything I've try seems to work in the web application, but not in the Web Service. In the Web Service, I still received the "Unable to open the physical file" error.

|||

Derek

If your still following this I solved my problem. Although I knew it wouldn't be an acceptable final answer I decided as another data point to set impersonation in the web service web.config file using my personal login information. That worked, which confirmed to me the problem definitely had to do with the permissions of the web service account (I don't alway trust error messages).

So then I looked again at the account information to the database file itself. When I created the database using Visual Studio, VS put the database in the web services data directory and created a weird sqlexpress account (SQLServer2005MSSQLUser$WD49832$SQLEXPRESS (WD49832\SQLServer2005MSSQLUser$WD49832$SQLEXPRESS)) which I had just accepted managed VS's access to the database. Well I shouldn't trust MS to be so kind. I still don't know the purpose of that account but it obviously doesn't handle the application's access. Once I added the users group to the database file and gave them the necessary permissions it worked. I'm going to claim that there were just to many things I didn't understand in the beginning so I stepped into accepting bad assumptions. The upside I've learned more than I would had it been easy.

Thanks for your company and help while I struggled through that one.

|||your welcome and to be honest you taught me that "user instances" something I never use YET have to be used in conjunction with Integrated (windows) authentication. (which makes sense)