Personal Edition

I am using the SQL Server Personal edition in remote offices with 2 or 3
clients each to connect to our central office server running SQL Server
Standard Edition. I want to use the personal edition because when I am
using replication between each client and main office and the EM will be
useful.
I am having connection problems when I use the personal edition but no
connection problems with the same setup using a developer edition. I
have read in places there are conection issues with the personal edition
but have not found many solutions or even details on the issues. It is
my understanding that the personal edition should work for this setup
and should communicate with Central servers and provide replication
capabilities.
My snapshot works fine but any transactions or Merge replication will
not replicate,I get a can not connect error. Again, the transactions
replicate when I use the developer edition on my client just not the
personal edition.
Can the personal edition communicate with standard edition? How does the
personal edition setup differ in order to work? Where can I find more
information on how to correct the problems (if any)? Are there any other
options (if this is not possible) without buying a stnd edition for each
office?
thanks,
eric.
Eric,
Personal Edition includes a concurrent workload governor that limits its
scalability; performance degrades when more than five Transact-SQL batches
are executed concurrently, which may be the cause of your issues. This is
really to prevent people using it for production systems, and upgrading to
workgroup or standard would solve your issues if this is the case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Permissions used to Connect to SSIS on remote machine

We are trying to take advantage of the new Security Context for SSIS but users are unable to connect remotely to the SSIS Service unless they have been added to the Administrators Users Group on the server. I have tried adding them to Guests, Power Users, Remote Desktop Users, Users and SQLServer2005DTSUser$machine but the user is unable to connect.

The message received when the user is unable to connect is:

Cannot connect to 192.x.x.x
Additional Information
-> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
-> Connect to SSIS Service on machine "192.x.x.x" failed:
Access is denied

What is the list of permissions and privileges that a user that does not belong to the server's Administrator group and have sysadmin server role, must have to allow them to create and run a package through SSIS.

i have the same issue. i am able to connect to the local integration services. but when i try to access another servers integration services Access is denied. what permissions are needed?

aaks

|||Try these instructions: http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx|||

You have to perform these steps in addition to the one's specified above.

http://mohansmindstorms.spaces.live.com/Blog/cns!69AE1BEA50F1D0E7!213.entry

Permissions used to Connect to SSIS on remote machine

We are trying to take advantage of the new Security Context for SSIS but users are unable to connect remotely to the SSIS Service unless they have been added to the Administrators Users Group on the server. I have tried adding them to Guests, Power Users, Remote Desktop Users, Users and SQLServer2005DTSUser$machine but the user is unable to connect.

The message received when the user is unable to connect is:

Cannot connect to 192.x.x.x
Additional Information
-> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
-> Connect to SSIS Service on machine "192.x.x.x" failed:
Access is denied

What is the list of permissions and privileges that a user that does not belong to the server's Administrator group and have sysadmin server role, must have to allow them to create and run a package through SSIS.

i have the same issue. i am able to connect to the local integration services. but when i try to access another servers integration services Access is denied. what permissions are needed?

aaks

|||Try these instructions: http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx|||

You have to perform these steps in addition to the one's specified above.

http://mohansmindstorms.spaces.live.com/Blog/cns!69AE1BEA50F1D0E7!213.entry

permissions sql express and remote connection problems TIA

posted to the forums a few weeks ago, followed the suggestion but the problem has not been squared, the link for my original post is at the bottom of this message.

have tried most of the suggestions in the forums, again see the link for details on steps i have tried, kept poking around and came up with a few more possibilities for the problem,

the error message is the one that says sql server express remote connnections are not enabled by default during the install of sql express,

backed up a little and it seems i can not even connect to the local machine, i ran the sqlcmd -s -e \sqlexpress and the login timesouts and says that sql express refused the connection, have shared memory protocal and tcp/ip running also tried it with named pipes,

so that left me wonderin if maybe permission for sql express need to be adjusted, brought up SQL server Studio Mangement Express console and went to the only express instance on the local machine, brought up properties for \sqlexpress and went to the permission section and noticed the following:

for the BuiltIn\administrator and builtIn\User account the only permission that is checked off is the "Conect SQL" which has only the Grant checkbox checked, also the grantor is sa, btw what does the checkbox With Grant do? this was the same for the both accounts,

for the public account the only permission that is enabled is the View any database,

are there other permission which need to be granted or "with granted", also i assume i should not be using the sa account or should i?

other things going on include include Visual Studio 2005, i am able to get to the database thru Server Explorer, and can bring up the tables and see them, sometimes thos their is a red x on the mdf, but when i click it brings them up.

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

Are you using a named instance? What instance names do you have in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL?

"sqlcmd -E -S \sqlexpress" will work only if the main instance is called sqlexpress, if it is called differently, then you have to use that name.

Have you ever been able to connect locally with sqlcmd? Let's figure out first what's wrong with the local connection, and then we can investigate the remote issues.

Thanks
Laurentiu

|||

Thanks Lauretiu, check the registry and it looks alright, the keys are below:

name SQLexpress

type reg_sz

data mssql.1

not sure if i have ever connected locally, have never been able to get either the sqlcmd -e -s \sqlexpress to work, or the aspnet_regsql wixard to work, (is ther anothe way to test the local connection?) for the sqlcmd i get a get a message like this,

HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine actively refused it

sqlcmd: Error Microsoft sql native client :an error has occurred while establishing a connection th the server. When conecting th SQL server 2005, this failure may be caused by the fact that unde rth edefault settings sql server does not allow remote connecitons. and the a login timeout expired.

the server has a different ip address than the laptop itself.

the aspnet_regsql error message is very similar,

|||

Here's a link that discusses some connection failures and mentions the 0x274D error:

http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx

According to Peter, one of the reasons for this message could be that TCP is not enabled. Could you check that the TCP protocol is enabled? You can use the Configuration Manager and look under SQL Server 2005 Network Configuration->Protocols for SQLEXPRESS. I'll see if I can find anything else about this error.

Thanks
Laurentiu

|||

This may be just a typo in the post but the correct syntax for the sqlcmd command run on the same machine where SQL Express runs is:

sqlcmd -E -S .\sqlexpress

Note:

- the capital -E and -S (the case matters in the option selection)

- the dot before the \ character

In your original post you mentioned that the SQL Express lists port 1232 under IPAll. If you get the same error ("TCP Provider: No connection could be made because the target machine actively refused it") after running the above sqlcmd statement, you can troubleshoot it by running:

telnet <machineName> 1232

- replace <machineName> with the actual name of your machine.

- if it fails with an error similar to "Could not open connection to the host" it means SQL Express is not configured for TCP, and may need to be restarted.

- if it gives you a blank screen it means it connected, and you should be able to connect through

sqlcmd -E -S tcp:.\sqlexpress,1232

|||

Hey Peter thank

it was the capital -S that was causing the problem, a weeks delay over a cap, should know better by now, thanks again, take care

permissions issue with http driven report in web iframe

Hi there,

I need to understand why I am getting the following error when I run my deployed (remote) report embedded in an iframe within a page of my web application.

error:
An

error has occurred during report processing. (rsProcessingAborted)
Query

execution failed for data set 'cnMOOR'. (rsErrorExecutingCommand)For

more information about this error navigate to the report server on the local

server machine, or enable remote error

This happens when I fire the following code from a commandbutton:
AppSettingsReader cfgApp = new AppSettingsReader();

string prefix = Convert.ToString(cfgApp.GetValue("reporturlprefix", typeof(System.String)));

string reporturl = prefix + "Client&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=False&supplierid=" + Session["supplierId"].ToString() + "&clientid=" + ddlClient.SelectedItem.Value + "&startdate=" + txtStartDate.Text + "&enddate=" + txtEndDate.Text + "&ownertype=1";

SpecificClient.Attributes.Add("src", reporturl);

SpecificClient is the ID of the Iframe

on the same page I created a link - which runs the report perfectly - no permission problems.
<a href="http://server2003msrs/reportserver?/moorreports/Client&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=False&supplierid=28&clientid=71&startdate=2007-01-01&enddate=2007-02-16&ownertype=1">Test</a>

I don't understand why these two controls are behaving differently.

I'd appreciate some guidance with this as the deadline looms...

Helen

Hi there,

something that I didn't mention - this error only happened when I changed the connection string properties. Previously both controls had worked fine.

I eventually fixed it by creating a new report whilst pointing at the new database.

suddenly all of them worked as before.

weird, but that's what solved the issue.

regards Helen

permissions issue with http driven report in web iframe

Hi there,

I need to understand why I am getting the following error when I run my deployed (remote) report embedded in an iframe within a page of my web application.

error:
An

error has occurred during report processing. (rsProcessingAborted)
Query

execution failed for data set 'cnMOOR'. (rsErrorExecutingCommand)For

more information about this error navigate to the report server on the local

server machine, or enable remote error

This happens when I fire the following code from a commandbutton:
AppSettingsReader cfgApp = new AppSettingsReader();

string prefix = Convert.ToString(cfgApp.GetValue("reporturlprefix", typeof(System.String)));

string reporturl = prefix + "Client&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=False&supplierid=" + Session["supplierId"].ToString() + "&clientid=" + ddlClient.SelectedItem.Value + "&startdate=" + txtStartDate.Text + "&enddate=" + txtEndDate.Text + "&ownertype=1";

SpecificClient.Attributes.Add("src", reporturl);

SpecificClient is the ID of the Iframe

on the same page I created a link - which runs the report perfectly - no permission problems.
<a href="http://server2003msrs/reportserver?/moorreports/Client&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=False&supplierid=28&clientid=71&startdate=2007-01-01&enddate=2007-02-16&ownertype=1">Test</a>

I don't understand why these two controls are behaving differently.

I'd appreciate some guidance with this as the deadline looms...

Helen

Hi there,

something that I didn't mention - this error only happened when I changed the connection string properties. Previously both controls had worked fine.

I eventually fixed it by creating a new report whilst pointing at the new database.

suddenly all of them worked as before.

weird, but that's what solved the issue.

regards Helen

Permission problems: Need help with CLR credentials

Hi everyone....

I have made a CLR stored procedure which goes to a shared folder on a remote computer. I am having permission problems when executing the stored procedure. (In case anyone is wondering, I am using the .NET SYSTEM.IO class)

I have done the following:

CREATE CREDENTIAL myuser
WITH IDENTITY = 'mydomain\myuser', SECRET = 'some56*Z';

CREATE LOGIN sam WITH PASSWORD = 'meowPw!a3';

ALTER LOGIN sam WITH CREDENTIAL = myuser;

...(other SQL so that SAM can execute the SP)

Now, I login to SQL Server using the new login, and when I try to execute the CLR stored procedure, I get "unknown username or bad password.".

I know 100% for sure that the account in the "MYUSER" credential has access. Is there something else I need to do?

You need to use Impersonation so that your CLR stored proc accesses external resources as your Windows user account, rather than the account that SQL Server is running under.

You can see an example of how to do this here: http://msdn2.microsoft.com/en-us/library/ms131068.aspx

If you are already doing this and it doesn't work, post the relevant clr code you're using.

Steven

Permission for Linked Server User

Dear,

What minimum permission do i need to grant to a Remote Login to create a linked server.

Regards

Sufian

I think you will need to use the sp_addlinkedsrvlogin proc to add the login to the linked server.

The minimum permissions for this is alter any login in SQL 2005.

To add the linked Server use the sp_addlinkedserver proc.

The minimum permission for this proc is alter any linked server

HTH,

-Steven Gott

SDE/T

SQL Server

|||

I have created a User say XXX with default database <master> with no other permission apart from Connect.

What other permission do i need to grant to user so that i can use that user as a remote Login in linked Server.

regards

sufian

|||

I am pretty sure you need to grant alter any login and alter any linked server to the login for the user XXX

I did a quick search and found a related article that may help

http://www.databasejournal.com/features/mssql/article.php/3691721

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Hi Steven,

Thanks for the link it helped in better understanding of Linked Server.

My question is still pending regarding the permission to user used in making connection to the remote server?

Well i got the resolution.

We have to grant only two permission to the user to use it in linked server creation.

GRANT CONTROL SERVER TO LinkedServer_User;

GRANT ALTER ANY LINKED SERVER to LinkedServer_User;

regards

Sufian

|||

Sufian,

The CONTROL SERVER permission implies ALTER ANY LINKED SERVER so if you really need CONTROL SERVER that permission alone should suffice.

I am surprised that this doesn't work:

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Can you confirm that this doesn't work? If it doesn't then I can open a bug with the product group.

Thanks,

-Steven Gott

SDE/T

SQL Server

|||

Created a new user abc and given readonly access to one database x only.

No other permission a part from DataReader to abc.

Created new linked server giving the username and password in the (Be made using the security context) option.

Linked server created successfully.

and i can only see the database on which user have read only access.

After granting below mentioed permission no effect on cataloge

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Well i think i need to give access to user on other databases also.

Please open a bug if it is.

Regards

Sufian

Permission for Linked Server User

Dear,

What minimum permission do i need to grant to a Remote Login to create a linked server.

Regards

Sufian

I think you will need to use the sp_addlinkedsrvlogin proc to add the login to the linked server.

The minimum permissions for this is alter any login in SQL 2005.

To add the linked Server use the sp_addlinkedserver proc.

The minimum permission for this proc is alter any linked server

HTH,

-Steven Gott

SDE/T

SQL Server

|||

I have created a User say XXX with default database <master> with no other permission apart from Connect.

What other permission do i need to grant to user so that i can use that user as a remote Login in linked Server.

regards

sufian

|||

I am pretty sure you need to grant alter any login and alter any linked server to the login for the user XXX

I did a quick search and found a related article that may help

http://www.databasejournal.com/features/mssql/article.php/3691721

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Hi Steven,

Thanks for the link it helped in better understanding of Linked Server.

My question is still pending regarding the permission to user used in making connection to the remote server?

Well i got the resolution.

We have to grant only two permission to the user to use it in linked server creation.

GRANT CONTROL SERVER TO LinkedServer_User;

GRANT ALTER ANY LINKED SERVER to LinkedServer_User;

regards

Sufian

|||

Sufian,

The CONTROL SERVER permission implies ALTER ANY LINKED SERVER so if you really need CONTROL SERVER that permission alone should suffice.

I am surprised that this doesn't work:

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Can you confirm that this doesn't work? If it doesn't then I can open a bug with the product group.

Thanks,

-Steven Gott

SDE/T

SQL Server

|||

Created a new user abc and given readonly access to one database x only.

No other permission a part from DataReader to abc.

Created new linked server giving the username and password in the (Be made using the security context) option.

Linked server created successfully.

and i can only see the database on which user have read only access.

After granting below mentioed permission no effect on cataloge

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Well i think i need to give access to user on other databases also.

Please open a bug if it is.

Regards

Sufian

Permission for Linked Server User

Dear,

What minimum permission do i need to grant to a Remote Login to create a linked server.

Regards

Sufian

I think you will need to use the sp_addlinkedsrvlogin proc to add the login to the linked server.

The minimum permissions for this is alter any login in SQL 2005.

To add the linked Server use the sp_addlinkedserver proc.

The minimum permission for this proc is alter any linked server

HTH,

-Steven Gott

SDE/T

SQL Server

|||

I have created a User say XXX with default database <master> with no other permission apart from Connect.

What other permission do i need to grant to user so that i can use that user as a remote Login in linked Server.

regards

sufian

|||

I am pretty sure you need to grant alter any login and alter any linked server to the login for the user XXX

I did a quick search and found a related article that may help

http://www.databasejournal.com/features/mssql/article.php/3691721

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Hi Steven,

Thanks for the link it helped in better understanding of Linked Server.

My question is still pending regarding the permission to user used in making connection to the remote server?

Well i got the resolution.

We have to grant only two permission to the user to use it in linked server creation.

GRANT CONTROL SERVER TO LinkedServer_User;

GRANT ALTER ANY LINKED SERVER to LinkedServer_User;

regards

Sufian

|||

Sufian,

The CONTROL SERVER permission implies ALTER ANY LINKED SERVER so if you really need CONTROL SERVER that permission alone should suffice.

I am surprised that this doesn't work:

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Can you confirm that this doesn't work? If it doesn't then I can open a bug with the product group.

Thanks,

-Steven Gott

SDE/T

SQL Server

|||

Created a new user abc and given readonly access to one database x only.

No other permission a part from DataReader to abc.

Created new linked server giving the username and password in the (Be made using the security context) option.

Linked server created successfully.

and i can only see the database on which user have read only access.

After granting below mentioed permission no effect on cataloge

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Well i think i need to give access to user on other databases also.

Please open a bug if it is.

Regards

Sufian