Showing posts with label own. Show all posts
Showing posts with label own. Show all posts

Tuesday, March 20, 2012

Changing "Connection String" in SSIS package ?

Hi!

I create a SSIS Package for ETL on my own machine. During development database was also on my machine. For access to this database an OLE DB connection was defined within a package in BI Development Studio. Everything worked well both in debug mode and testing package itself.

Finally I need to load data to a database on a different machine using this package.

I used several scenaries:

1) simply copied the package-file to estination machine, open it for execution, in section "Connection Managers" I edited connection string manually - changed server name and Initial Catalog. And try to execute.

2) on the destination machine I manually created an OLE DB connection (using Microsoft Data Link) to a different database (test succeded), Changed the extention of the connection file 'udl' for ' txt ' and copied its connection string to the field connection string in section "Connection Managers" (pointed in variant 1) ).

3) use Package Configurations, copied the deployment to destination machine, installed the package the way like written here - http://msdn2.microsoft.com/en-us/library/ms365338.aspx. Changed exported properties - Server name, Initial Catalog and also the whole Connection String. Also try to execute.

In all cases I recieved the same error execution message :

"Errors in the metadata manager. Either the database with ID of " OLD_DATABASE_NAME " does not exist in the server with ID of " NEW_SERVER_NAME " or the user does not have permissions to access the object."

As for access (username/pass) settings they are the same for both of them, I have the same administrative rights on both machines. And more with the same rights the ole db connection made was made manually in variant 2 - succeded!!! So I don't think the problem is here.

As for Error message - I think somewhere the OLD name of database (Initial Catalog) is saved, though I tried to change it. Though the NEW value for the server name is substituted.

Please, help me. I don't know what else can I try. And it is not a single case for my practice. So I think - something wrong in my actions.

Don't store the ConnectionString property AND all of the other properties as configurations. Store EITHER the ConnectionString property OR all of the other properties (my advice would be to store only the ConnectionString property)

-Jamie

|||

Jamie Thomson - why to store only the ConnectionString?

Then config-file will overwrite only Connection String value itself, written in the package. But other properties would not be changed, but they will also been included into the package (the content of the package is independent with what properties I decided to include into configurations. Am I right? ).

Will you explain me? Thanks.

|||

The reply, posted before Jamie Thomson's one, was very helpfull to me. I marked it as it was helpful, but suddenly your post disapeared ? Sorry, I am new to this forum, but anywhere you help me.

I tried to watch the package file in text editor, and find the property to which the old database name value was saved, then include this property to configurations to make it changable at run time.

Thanks, it works!!!!

|||

Was this the post that disappeared?

sachin.rao wrote:

Hi Iris

The only place it could be persisting this information is in the package itself. Is the catalog name being stored in the dtsx file? Open it up in your favourite text editor and check it out.

Sachin

|||

Rafael Salas - yes, it is! Thanks.

Please, tell me what I did wrong and how can I mark a post if it was helpful and/or was a reply/solution to my question.

|||For some reason that post was deleted...only the author or a moderator can do that...I don't know who did it. You should have a buton 'Mark as anserwer'...|||

IrisFresco wrote:

Jamie Thomson - why to store only the ConnectionString?

Then config-file will overwrite only Connection String value itself, written in the package. But other properties would not be changed, but they will also been included into the package (the content of the package is independent with what properties I decided to include into configurations. Am I right? ).

Will you explain me? Thanks.

Because the ConnectionString is made up of allthose individual properties (e.g. CatalogName, Username, password, ServerName etc...). Changing ConnectionString automatically changes allthe other properties. With ConnectionString you only need to maintain one thing however and I see that as an adavantage.

-Jamie

Monday, March 19, 2012

changeobjectowner permission

Hi
Is it possible that an user database (without server functions) own permissi
on on sp_changeobjectowner?Antonio,
The BOL says the following: Only members of sysadmin fixed server role, the
db_owner fixed database role, or a member of both the db_ddladmin and
db_securityadmin fixed database roles can execute sp_changeobjectowner.
As far as I know, this is correct. Are you having an experience that
suggests otherwise?
Russell Fields
"Antonio" <anonymous@.discussions.microsoft.com> wrote in message
news:C79A1502-34D7-4665-8BBE-489170481915@.microsoft.com...
> Hi
> Is it possible that an user database (without server functions) own
permission on sp_changeobjectowner?
>

changeobjectowner permission

Hi
Is it possible that an user database (without server functions) own permission on sp_changeobjectowner?
Antonio,
The BOL says the following: Only members of sysadmin fixed server role, the
db_owner fixed database role, or a member of both the db_ddladmin and
db_securityadmin fixed database roles can execute sp_changeobjectowner.
As far as I know, this is correct. Are you having an experience that
suggests otherwise?
Russell Fields
"Antonio" <anonymous@.discussions.microsoft.com> wrote in message
news:C79A1502-34D7-4665-8BBE-489170481915@.microsoft.com...
> Hi
> Is it possible that an user database (without server functions) own
permission on sp_changeobjectowner?
>

Thursday, February 16, 2012

Change report server database

I install reporting service on 2 machines. Each RS has it's own report
server database. e.g. A_ReportServer for machine A and B_ReportServer for
machine B. Can I dynamically change the report server database ? i.e.
Machine A point to B_ReportServer database. If yes, how can I do this ?You can redirect A_ReportServer to point to Database B by running the
rsconfig.exe utility. Please refer to the following link for more
information:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSinstall/htm/gs_installingrs_v1_7oft.asp
If your looking for load balancing or failover support as in load balancing
of the web servers or Sql Server failover that is available with the
enterprise edition. Check the BOL for more info about how to configure.
"May Liu" wrote:
> I install reporting service on 2 machines. Each RS has it's own report
> server database. e.g. A_ReportServer for machine A and B_ReportServer for
> machine B. Can I dynamically change the report server database ? i.e.
> Machine A point to B_ReportServer database. If yes, how can I do this ?

Tuesday, February 14, 2012

Change Password

I created a SQL login name which I'm specifically intending to use as the
login id that can allow a user to change their own password. I currently
have this login id permission checked off as 'Security Administrators' in
the Server Roles tab but I noticed by checking this off it gives all of the
other security permissions listed. Is there a way to only allow this
specific login id to change passwords but not any other security permission?
Can anyone advise if this is a safe practice or should I be doing a
different approach in letting users change their own passwords?No...not a safe practice. Users can change their own
passwords without needing to implement any security changes.
Check books online and look up sp_password.
By default, public has execute permissions on this system
stored procedure. Logins that aren't members of sysadmins or
securityadmins can only change their own passwords. That is
what you would want.
-Sue
On Thu, 27 Apr 2006 11:07:53 -0700, "J"
<IDontLikeSpam@.Nowhere.com> wrote:

>I created a SQL login name which I'm specifically intending to use as the
>login id that can allow a user to change their own password. I currently
>have this login id permission checked off as 'Security Administrators' in
>the Server Roles tab but I noticed by checking this off it gives all of the
>other security permissions listed. Is there a way to only allow this
>specific login id to change passwords but not any other security permission
?
>Can anyone advise if this is a safe practice or should I be doing a
>different approach in letting users change their own passwords?
>|||Thanks a bunch for your reply Sue. I really appreciate it.
When you mean the Public role are you referring to the Public role in my
user defined database or the master database? I tried to execute
sp_password from my .asp page to change my own password being logged in as a
regular database user and am getting the following error message:
'Only members of the sysadmin role can use the loginame option. The password
was not changed.'
Thanks Sue.
J
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:g8b2529vut87cmv4491mgimg07lfnr6ve4@.
4ax.com...
> No...not a safe practice. Users can change their own
> passwords without needing to implement any security changes.
> Check books online and look up sp_password.
> By default, public has execute permissions on this system
> stored procedure. Logins that aren't members of sysadmins or
> securityadmins can only change their own passwords. That is
> what you would want.
> -Sue
> On Thu, 27 Apr 2006 11:07:53 -0700, "J"
> <IDontLikeSpam@.Nowhere.com> wrote:
>
>|||Actually I just found out that I just needed to remove the last parameter of
supplying the login id and it worked fine.
Thanks again :-)
J
"J" <IDontLikeSpam@.Nowhere.com> wrote in message
news:eMPVF0kaGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Thanks a bunch for your reply Sue. I really appreciate it.
> When you mean the Public role are you referring to the Public role in my
> user defined database or the master database? I tried to execute
> sp_password from my .asp page to change my own password being logged in as
> a regular database user and am getting the following error message:
> 'Only members of the sysadmin role can use the loginame option. The
> password was not changed.'
> Thanks Sue.
> J
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:g8b2529vut87cmv4491mgimg07lfnr6ve4@.
4ax.com...
>|||No problem...thanks for posting back.
-Sue
On Thu, 27 Apr 2006 16:31:46 -0700, "J"
<IDontLikeSpam@.Nowhere.com> wrote:

>Actually I just found out that I just needed to remove the last parameter o
f
>supplying the login id and it worked fine.
>Thanks again :-)
>J
>
>"J" <IDontLikeSpam@.Nowhere.com> wrote in message
>news:eMPVF0kaGHA.3532@.TK2MSFTNGP05.phx.gbl...
>