Permissions for Connecting to Reporting Services in Management Studio

Hi,

We have a user in the System Administrator and System User roles of

2005 SSRS. He is able to connect to the website

(http://<servername>/Reports) and do whatever he needs to do.

But, when he tries to connect to Reporting Services in Management

Studio, he receives the following error:

===================================

Cannot connect to <servername>.

===================================

The machine could not

be found

(Microsoft.SqlServer.Management.UI.RSClient)

Program

Location:

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String

serverName)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo

ci, IServerType server)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

I added him to the Distributed COM Users group, in DCOM security I

added him to the Default Launch and Activation Permissions (Local

Launch, Remote Launch, Local Activation, Remote Activation), I gave him

all permissions to the root namespace (and sub-namespaces) in wmi.

After giving him these permissions, the error message changes slightly

(no longer does he get 'the machine name cannot be found'):

Program

Location:

at

System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32

errorCode, IntPtr errorInfo)

at

System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String

serverName)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo

ci, IServerType server)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

If

I add him to the local administrators group, he can connect using

Management Studio. But, that is not what we want to do. And, in fact,

we want to revoke any of these extra permissions that we have assigned

in order to try to get this working. Does anyone know what permissions

a person needs (likely in the OS) for connecting to Reporting Services

in Management Studio?

Thanks,

Michelle

Try granting permissions on \root\Microsoft\SqlServer\ReportServer\v9 namespace to Everyone.

As a workaround you can type in the url to report server instead of machine name. This will cause management studio to not go through the WMI code path to resolve report server url.

|||Thanks! The workaround worked. The dba used

http://<servername>/reportserver for the server name in

Management Studio and is able to connect to Reporting Services.

Michelle

Permissions for Connecting to Reporting Services in Management Studio

Hi,

We have a user in the System Administrator and System User roles of

2005 SSRS. He is able to connect to the website

(http://<servername>/Reports) and do whatever he needs to do.

But, when he tries to connect to Reporting Services in Management

Studio, he receives the following error:

===================================

Cannot connect to <servername>.

===================================

The machine could not

be found

(Microsoft.SqlServer.Management.UI.RSClient)

Program

Location:

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String

serverName)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo

ci, IServerType server)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

I added him to the Distributed COM Users group, in DCOM security I

added him to the Default Launch and Activation Permissions (Local

Launch, Remote Launch, Local Activation, Remote Activation), I gave him

all permissions to the root namespace (and sub-namespaces) in wmi.

After giving him these permissions, the error message changes slightly

(no longer does he get 'the machine name cannot be found'):

Program

Location:

at

System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32

errorCode, IntPtr errorInfo)

at

System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String

serverName)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo

ci, IServerType server)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

If

I add him to the local administrators group, he can connect using

Management Studio. But, that is not what we want to do. And, in fact,

we want to revoke any of these extra permissions that we have assigned

in order to try to get this working. Does anyone know what permissions

a person needs (likely in the OS) for connecting to Reporting Services

in Management Studio?

Thanks,

Michelle

Try granting permissions on \root\Microsoft\SqlServer\ReportServer\v9 namespace to Everyone.

As a workaround you can type in the url to report server instead of machine name. This will cause management studio to not go through the WMI code path to resolve report server url.

|||Thanks! The workaround worked. The dba used

http://<servername>/reportserver for the server name in

Management Studio and is able to connect to Reporting Services.

Michelle

Permissions for Connecting to Reporting Services in Management Stu

Hi,
We have a user in the System Administrator and System User roles of 2005
SSRS. He is able to connect to the website (http://<servername>/Reports) and
do whatever he needs to do. But, when he tries to connect to Reporting
Services in Management Studio, he receives the following error:
===================================
Cannot connect to <servername>.
===================================
The machine could not be found (Microsoft.SqlServer.Management.UI.RSClient)
--
Program Location:
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
I added him to the Distributed COM Users group, in DCOM security I added him
to the Default Launch and Activation Permissions (Local Launch, Remote
Launch, Local Activation, Remote Activation), I gave him all permissions to
the root namespace (and sub-namespaces) in wmi. After giving him these
permissions, the error message changes slightly (no longer does he get 'the
machine name cannot be found'):
Program Location:
at
System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
errorCode, IntPtr errorInfo)
at
System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
If I add him to the local administrators group, he can connect using
Management Studio. But, that is not what we want to do. And, in fact, we want
to revoke any of these extra permissions that we have assigned in order to
try to get this working. Does anyone know what permissions a person needs
(likely in the OS) for connecting to Reporting Services in Management Studio?
Thanks,
MichelleMichelle wrote:
> Hi,
> We have a user in the System Administrator and System User roles of 2005
> SSRS. He is able to connect to the website (http://<servername>/Reports) and
> do whatever he needs to do. But, when he tries to connect to Reporting
> Services in Management Studio, he receives the following error:
> ===================================> Cannot connect to <servername>.
>
> ===================================> The machine could not be found (Microsoft.SqlServer.Management.UI.RSClient)
> --
> Program Location:
>
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
> at
> Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
> I added him to the Distributed COM Users group, in DCOM security I added him
> to the Default Launch and Activation Permissions (Local Launch, Remote
> Launch, Local Activation, Remote Activation), I gave him all permissions to
> the root namespace (and sub-namespaces) in wmi. After giving him these
> permissions, the error message changes slightly (no longer does he get 'the
> machine name cannot be found'):
> Program Location:
>
> at
> System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
> errorCode, IntPtr errorInfo)
> at
> System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
> at
> Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
> If I add him to the local administrators group, he can connect using
> Management Studio. But, that is not what we want to do. And, in fact, we want
> to revoke any of these extra permissions that we have assigned in order to
> try to get this working. Does anyone know what permissions a person needs
> (likely in the OS) for connecting to Reporting Services in Management Studio?
> Thanks,
> Michelle
Hi Michelle,
I'm having a very similiar problem to yours and I was wondering if you
ever found a solution to your problem. I haven't found much about it
on the internet yet, but I'm going to continue my search today. I'll
let you know if I find anything.
Thanks,
Rob

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)

Permission levels for MSDE, ASAP

We seem to have a problem with permission levels and connecting to an MSDE (MSSQL) server. If the user is under the Domain Admins group, the the access projet (front end) will open correctly and connect to the data server. If they are not part of that group then the front end can ever establish a file to the database server. We do not want to make all the users Domain Admins, so is there a way to make MSDE let them trough even though they are on a lower level.

I've done many tests, and also tried many things. I've even went to the extent to give Full Control to the whole MSSQL folder in program files for Everyone. I have made sure that the database file itself inherieted it's parents security settings, which were what I had just described.

Any ideas how how to make MSDE let anyone connect? Thanks in advance!Domain admins are allowed into the database by virtue of the fact that they are included in the Local Administrators group in the machine running MSDE. So, the quickest thing you could do is make your users local admins, rather than domain admins. In order to reduce their permissions further, you will have to settle what you want the users to be able to do. Local Admins can do anything to the instance of MSDE (configure settings, drop/add databases, forget to backup their data, etc.), but then, these are MSDE users, so they are usually only affecting themselves.|||Thanks again for replying so quickly! I am a little unsure what you mean by local admin though. It is an NT server, by the way. There is no group called Local Admin. I tried the group Administrators, but that didn't work. Do you mean on each workstation?|||We may have to back up a step here. MSDE is usually installed on workstations (my assumption, my bad). You have one central server, with various workstations connecting to the instance of MSDE on that server, then? Now we have a bit of a rat hole. Welcome to the world of SQL Server permissions. In other words, not much is going to be automatic here. Here is how you will likely have to start:

1) Create a domain group for the users who need to use the application.

2) On the server, with a command window, run
osql -E (-S servername\instancename)

NOTE: You may need to supply the MSDE instance name. If you get a numbered prompt, you are in.

3) switch to the application database by running the following:

1>use (dbname)
2>go

4) Run the following commands:

1>exec sp_grantlogin '(domain\new group from above)'
2>exec sp_grantdbaccess '(domain\new group from above)'
3>exec sp_addrolemember db_owner, '(domain\new group from above)'

Once you have done all that, you should be able to run without making people domain admins. I am not too wild about having you grant db_owner to the users, but they would have had sysadmin otherwise, but that is a long debate between programmers and DBAs that I probably do not need to invoke here. Good luck.|||I thank you very much for getting back with me. I apoligize for not replying in a few days, as I was vey busy. The method you described is what I was basically looking to do. I did exactly what you said, but it did not seem to work. I'm afraid I've done it wrong. I just wanted to make sure of the notiation you meant for me to use when typing it. Let's say for example:
1. the user group is called SQL Users
2. the domain is called DOMAIN
3. the server is called SERVER1
4. the database is called IT Help Desk

Would this be correct?

osql -E -S SERVER1

1>use [IT Help Desk]
2>go

1>exec sp_grantlogin '(DOMAIN\SQL Users)'
2>exec sp_grantdbaccess '(DOMAIN\SQL Users)'
3>exec sp_addrolemember db_owner, '(DOMAIN\SQL Users)'

Also...the single quotes...in the command prompt, there are open and close ones. The one that is usually used for both (located near the enter key on he keyboard) shows up as a close single quote. So what should be used? An open then close one? Both closed? Just wondering. If anyone else knows, feel fee to post as well.|||can you access the database thru sql enterprise manager?|||I'm using MSDE!! This is the free version of MSSQL that comes with MS Office. There is no EM for it.|||Sorry. If you lose the parentheses, you should be able to get it to work. If not, post whatever output (error message) you do get.|||It's not really an error message. It's just not able to connect to the server because of permission settings. I know this, but I can't get the server to let anyone connect. I tried without the pathrenthesis, but it still didn't work. TO be more detailed, when in MS Access, and trying to open a form (the point at which it needs to talk to the server) it gives me a run time error 18456, which says that the database could not log onto the server and to verify that the log on info is correct. I know that it's the groups that is the issue, because when I add the user that I am currently logged on as in the Domain Users group, it works fine. I think you were going in the right direction. I just wish MSDE had an interface to work with. I hate this stupid command line stuff. Too much coding. I'm not a professional programmer, altough I have taken VB a few years ago and know most of that. Other then that, web programming is all I know. Anyways, I'm heaing home for the holidays, this project isn't top priority, so I'll work on it when I get back. If you have any other ideas, please let me know. If you want to contact me directly, please email pilotboi@.comcast.net, thanks! Happy holidays!|||I had a similar problem with XP. If using Windows XP your (regular) local user will have very restricted user rights concerning writing to the registry. In addition there may be problems using the .NET framework and MSDE.
It may not be enough for installing or even using the MSDE. The MSDE requires writing to the registry, therefore the user does not need administrator rights but local user rights of a poweruser. We had the same problem. Have a look to the MSDE installation guide and/or readmes and the security settings of your .NET framework. This was helping us, the error messages did not lead to the right conclusions.|||You can use 3rd party tools to manage MSDE and the Enterprise Manager