Here's the situation we're facing. Our organization is going to consolidate into a single domain. The plan of action is to disband the old domain, and create an all new domain. Most of our servers will also be renamed in the process.
We've got something of a two-server setup, with one machine handling SQL Server, and the other hosting various intranet sites, including Reporting Services 2000.
SQL Server itself shouldn't be a huge problem to migrate - the machine only has about half a dozen Windows logins set up. In my experience, though, Reporting Services is a much more fragile animal. Just getting it up and running on Win2003 SP1 and coexisting with ASP.NET 2.0 was enough of a stunt already. What will I have to watch out for to make sure this doesn't completely hose our installation? Would it be too optimistic for me to hope that I'll just have to reconcile any domain service accounts, change the connection string for the report server catalog database, and be done with it?
We successfully migrated our RS2000 to RS2005. But we run into an issue €“ trying to modify an existing subscription that was created on RS2000 (We do not have this problem if the subscription is created on RS2005).
The error message is:
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help. The specified @name (€˜B3F7CB60-6636-4F67-93CF-B94C052DFD29€™) already exists.
The call stack is: w3wp!library!d!02/05/2007-14:31:10:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Data.SqlClient.SqlException: The specified @name ('B3F7CB60-6636-4F67-93CF-B94C052DFD29') already exists. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.SqlAgentScheduler.CreateTask(Task task) at Microsoft.ReportingServices.Library.SqlAgentScheduler.UpdateTask(Task task) at Microsoft.ReportingServices.Library.SchedulingDBInterface.UpdateTaskProperties(Task task, Boolean updateSqlAgentSchedule) at Microsoft.ReportingServices.Library.TimedSubscriptionHandler.ValidateSubscriptionData(Subscription subscription, String subscriptionData) at Microsoft.ReportingServices.Library.SubscriptionManager.ValidateSubscriptionData(Subscription subscription, String eventType, String subscriptionData) at Microsoft.ReportingServices.Library.SubscriptionManager.SetSubscriptionProperties(Guid id, String eventType, String matchData, ExtensionSettings extensionSettings, String description, ParameterValueOrFieldReference[] parameters, DataRetrievalPlan dataSettings) at Microsoft.ReportingServices.Library.RSService._SetSubscriptionProperties(String subscriptionID, ExtensionSettings extensionSettings, String description, String eventType, String matchData, ParameterValueOrFieldReference[] parameters, DataRetrievalPlan dataSettings) at Microsoft.ReportingServices.Library.RSService.SetSubscriptionProperties(Guid batchId, String subscriptionID, ExtensionSettings extensionSettings, String description, String eventType, String matchData, ParameterValueOrFieldReference[] parameters, DataRetrievalPlan dataSettings) --- End of inner exception stack trace ---
The error message is raised from the stored procedure sp_verify_job:
-- NOTE: We allow jobs with the same name (since job_id is always unique) but only if -- they originate from different servers. Thus jobs can flow from an MSX to a TSX -- without having to worry about naming conflicts. IF (EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE (name = @name) AND (originating_server = @originating_server) AND (job_id <> ISNULL(@job_id, 0x911)))) -- When adding a new job @job_id is NULL BEGIN RAISERROR(14261, -1, -1, '@name', 'name') RETURN(1) -- Failure END
which is called by another stored procedure sp_add_job.
Question: Is there anyway that I can workaround this issue? Or is there anyway that I can re-create existing report server subscriptions (I have 300+ subscriptions) programmatically.
we recently migrated from our in-house domain to the Enterprise domain. Everything went smooth except for the fact that I can no longer accept my dBs using my SA or my domain admin account. There is only 1 account I can get into the management studio with but it has no admin privileges, so I can't make any  password changes or add accounts. I don't have a test environment so kind of hesitant to experiment with our production system.
We have a network setup with two domain controllers, DC1 and DC2, working independently from eachother along with a DBserver1 that runs a BCM database and is a member of DC1. For certain reasons we would like to demote the DBserver1 and join it on the domain of DC2. What are the steps required in order to properly move a BCM Database running on SQL2005 to a new domain, where the security data lies in the active directory of DC1?
I am trying to migrate users accounts from a 2000 Server to 2003 server I am changing domain names and the new domain a is a child domain off a parent domain. I have created a trust on both domain servers and the parent domain and I have created administrative users on each domain I am using the ADMIT migration tool and can get all the way through it then get an access denied when it trys to create the accounts. The knowledge bases on this say I need a Domain Admin user on each domains for the other domain. Being in a child domain it does not let me create this I have created users and added the admistrative group for each user this should give the rights to create the users on the new domain, but still am getting the access denied.
Does anyone know what I am missing on this? Any help would be greatly appreciated.
We have a problem with our RS2005 after migration into another domain.
The problem is that we have got duplicate access rights for the same AD usergroup. When trying to remove or add a new AD group we get the error msg - "... role assignment is not valid. The role assignment is either empty or it specifies a user or group name that is already used in an existing role assignment for the current item. (rsInvalidPolicyDefinition)"
Books online gives no answers and neither does other serach engines .....
We want to start working with Reporting Services on a Windows Server 2003 (SP1) server that has SQL2000 (SP3) Standard Edition. I thought I could load RS from the CD but it does not appear to be on there. I seem to remember that Microsoft added RS to SQL2000 in the last year or so. Can I download a copy of it, or how do I go about getting a copy and how to I show my boss that it is a legit copy? Thanks...
We just applied SP2 to our Windows 2003 server & it has had an odd impact RS2000. When trying to change the data source of a report to a shared data source, the browse button when clicked does not take you to the page to enable selection! It does nothing!
Does anyone have any ideas on what's going on there?
-A "master domain" AD, a "sub domain" AD, a trust relationship between the two (sub trust master) -A sql server 2005 on a win server 2003 in "sub domain" AD -A linked server to "sub domain" AD -A linked server login using a "sub domain" admin acccount -A view to this linked server -A grant on masterDomain/Domain Users to the database -A grant on subDomain/Domain Users to the database -We want all connections done through "Windows Authentication" not "Database Authentication".
Queries on the view work fine using "sub domain" user accounts. Queries on the view fail using "master domain" user accounts (including master domain admin accounts)
"Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation."
All connections are done through "Windows Authentication" not "Database Authentication".
Can we establish cross domain connectivity with "Windows Authentication" ?
Below are details of the implementation:
SELECT TOP (100) PERCENT * FROM OPENQUERY(ADSI, 'SELECT displayname, givenName, sn, cn (etc...) FROM ''LDAP://OU=PEOPLE,DC=subDomain,DC=com'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' ')
In SQL Server Mngt Studio in Server Objects/Linked Servers/Providers/ ADSI properties security tab I have:
"connections will: <be made using this security context> Remote login:'subDomainAdminAccnt' With password: 'subDomainAdminAccntPassword'
Error: Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT displayname, givenName, sn, cn
FROM 'LDAP://OU=PEOPLE,DC=subDomain,DC=com'
WHERE
objectCategory = 'Person'
AND objectClass = 'user'
" against OLE DB provider "ADsDSOObject" for linked server "ADSI".
After using ADMT to migrate the domain user or group into the root domain, when I use enterprise manager to try and change the permissions allocated to that domain user/group, i get the 'Error 15401 NT user or Group not found'.
This is a correct error as the user is now in the root domain, however sql (in sysxlogins) still thinks its in the child domain.
Is there a simpler way, other than collecting the users permissions, deleting the user from SQL then adding back in with the correct domainusername format, then adding the permissions back?
I tried renaming the 'name' in sysxlogins (not recommended) and while that worked, whenever I tried to add the migrated user to another database, the login name was missing and would not resolve.
I believe it is something to do with the SID not matching.
I've got a RS2000 report from Analysis services 2000. This report is working fine but after the upgrade to SQL2K5, i'm having issues with changes to parameters.
Basically i'm passing DateQuarter, CustomersLevel as parameters to the following mdx script. I've tried to change mdx script in RS2005 but no luck yet. I don't much about the parameter level changes in sql2k5. So can anyone suggest what is wrong in the script.
Code Block RS2000
="with " & "member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)' " & "set [Rolling Quarters] as '" + Parameters!DateQuarter.value +".lag(4):" + Parameters!DateQuarter.value + "'" & "SELECT " & "{[Measures].[Ex Price]} ON COLUMNS , " & "filter({CrossJoin([Items by Class].[Item Class Description].members, [Rolling Quarters])}, [Measures].[Ex Price] > 0) on Rows " & "FROM Sales " & "WHERE (" & Parameters!CustomersLevel.Value & ")"
RS2005
with member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)' set [Rolling Quarters] as '@DateQuarter' +'.lag(4):' + '@DateQuarter' SELECT {[Measures].[Ex Price]} ON COLUMNS , filter({CrossJoin([Items by Class].[Item Class Description].members, [Rolling Quarters])}, [Measures].[Ex Price] > 0) on Rows FROM Sales WHERE STRTOSET(@CustomersLevel)
We're using RS2000 and there are some reports that are having problems exporting to Excel after having been rendered.
The reports have no more than 30,000 rows, but are about 150 columns wide. The reports run (execute and redner) very quickly in Reporting Services, but when exporting, the "blank rendering window" (the one with the URL: "http://ServerName.Reportserver?ReportName&rs%3aCommand=Render&rs%3AFormat=EXCEL) is open for about half an hour, and the w3wp process pummels our 8 proc box (CPU between 50 and 90% for that time) and memory goes past 1.5 GIG (of the total 8 GIG) usage.
Sometimes we'll get a message asking the user to log in after about 10-15 minutes, after which it fails, other times it makes it to the dialog box asking if you want to open or save the spreadsheet. If it makes it to this dialog, it's OK, if not, it fails on the export. The resulting spreadsheets are only between 25 - 32 MG, not very big at all.
Are there any settings we can tweak to speed this up, or at the very least, be sure it always makes it to the Open/Save dialog?
Hi There I've not managed to find an answer already about this, and so I know you guys will be able to help me out becuase your great!!!
Our Reporting Services portal is acting strangely, all the existing reports we have subscriptions for are running fine.
However should we change the list of contacts or the dates or times of any exisiting schedule this stops our reports from being delivered, infact the report run time will pass, but the report will not run and even in the Subscriptions table of RS the last status will only show "New Subscription".
So whatever is happening, it is not triggering the correct work flow. (Thats my thought at least) We examined SQL Agent logs to uncover that the Login used to run the reports was running the existing reports without issue, however the new subscriptions would give the following error:
[298] SQLServer Error: 8198, Could not obtain information about Windows NT group/user 'SERVERNAMEREPORTING_ACCOUNT'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
The server name and account have been changed to protect the gullible and innocent.
I do hope someone else can provide some insight into my issues
we're all pretty fluent with 2005 products, but must enhance our legacy 2000 RS product, and prefer to use vs2003. Does anyone recall how we used to get vs2003 to allow RS2000 design. We dont believe we saw such an option in installation of vs2003, but since installing the db preceded that, arent sure if we would have been given such an option had vs2003 been installed prior to the 2000 db.
Occasionally I have this error happening in some of my subscriptions: Subscription contains invalid parameters.
What is actually happenign, one of the parameters has lost the value on which it was previously running without problems. If I open subscription the first parameter, of more, is having no value. The value that is usually lost is first option in drop down list, being for ALL of bellow values. When the error happens it has no value, and that option normally doesn't exists as a parameter value. (If you go creating new subscription that same parameter doesn't have empty values as options to be selected. I usually return ALL and then other individual values.)
For example, one report is having paramteres that returns values from the stored procedure, and the return dataset would be:
Value Label ----------------- ----- 'SP1','SP2','SP3' ALL 'SP1' SP1 'SP2' SP2 'SP3' SP3
So, when the error happens, in no reason (no changes to proc, to report design, to subscription,...) usually those subscriptions that had ALL for this parameter have lost the value, the option is BLANK, and is treated as invalid value, which is correct, as my query also doesn't return BLANK value. I have to manually go into subscription and set up the value back to ALL and then it's OK, untill next time.
Has anyone experienced this problem?
Does anyone now the fix for this? We thought that hot fix on SP2 may solve this, we applied on dev server but it didn't.
I want to query data from a cube in analysis services with parameters in reporting services. I'm not sure how to make my select statement. Can someone help me please
I had reports using the HTML Viewer working in RS 2000. The servers were both O/S Windows 2000, SQL Server 2000, IIS 5.0, Reporting Services 2000) The default toolbar and parameters area were formatted and looked fine.
We just migrated to new servers with Windows 2003 Server, SQL Server 2005, IIS 6.0, Reporting Services 2005. I have the reports working fine...except the toolbar and parameters areas look as if there is no formatting at all...as if the style sheet isn't being applied (not even the default one --see below).
Although I don't think this is necessary....I tried the following to resolve the issue....
I'm trying to run a test from my test environment which is a non-domain Windows 2000 server to access my domain 2003 with SQL2005. I have install 2005 tools to try to access the SQL server.
- I have try following the KB265808 - no success. - Reading alot of blogs and it seems all are pointing to the same problem. "Remote access" but the settign is enabled.Error Message:
TITLE: Connect to Server ------------------------------
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
Question: Could Windows 2003 security be blocking access? I'm using sa account to access.
Also, sa account does not seems to work for remote access. It is ok when accessing locally.
Hi all,it happen to me a strange problem:i have a mdb file (in Access 2K) with SQL Server 2K linked tables whoruns on a workstation which is on a different domain that the SQLServer. It works.If i create a mdb file from a workstation which is a the domain of theSQL Server and then i run it a my non-domain workstation i have errormessage:Login failed for user '(null)'. Reason: Not associated with a trustedSQL Server connectionBut if i reattached my tables it works.If someone have an idea....PS: same ODBC on both machines
I'm trying to set up replication from one SQL server to another.
The publishing server is not a member of a domain and is located in a hosting center (but we have full control over the server). I can set up a Snapshot publication just fine.
The subscribing server is located in another remote location and is a member of a domain. Here I can also set up the subscription without errors.
The errors, I think, comes when the snapshot is about to be created, the error is, on the publisher server:
[298] SQLServer Error: 18456, Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. [SQLSTATE 28000]
And the snapshot is not created.
Is it even possible to set up replication like this. I need to transfer the data from one sql server to another so we have a working "backup" so to speek if the other server does not respond.
Guys, I have problem in adding the user. The domain name "scs" that I am logged on a machine with user name "rao", But I am not able this user to SQL Server as scs/rao, it gives that the not found.
I am in the process of installing SQL Server 2005. Under Service Account I am selecting the domain user account option. It asks for the username, password and the domain name. How can I find out what the domain name is?
A while back I asked this in the SQL security forum, but did not receive any replies. I feel that this is a fairly basic, common question, so I am posting it here in the hopes that this forum has higher traffic and that someone here will know the answer.
I am trying to connect as follows:
Server: Windows 2003, SQL 2005, on a domain Client: Windows 2008 Beta, not on any domain
I created an account with the same user name as the domain user on the client machine. And then I logged in as that user and went to Manage Network Password. I entered the correct domain credentials. Verified that this worked for file shares. However, SQL does not appear to be recognizing this and it tells me:
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
I have verified that this domain account is working properly with SQL when the client is also on the domain.
Q: How can I get this Windows authentication scenario to work where the client is not on the domain and the SQL server is on the domain?
(Note: A similar case that can also occur frequently is that the server and client are on different domains.)
I have developed a database on my local machine using MSDE. I am using Visual Studio.net to add tables/stored procedures to the database. I have created asp.net pages. I need to get this database on a web server that runs Enterprise SQL Server. Can I just use the 'sp_detach_db' and 'sp_attach_db' to move the database between servers?
Hi Everyone, I am migrating my old sql server database to the new hardware, in the processes I migrated all the databases from old sql server 7.0 to new sql server 7.0, and also generated the scripts for all jobs and executed in the new server, all the jobs appear but when I go and see in the job steps, I don't see any steps in the jobs, I tried different ways but I am not able to get the steps. Can any one help me out.............. I have about 160 jobs so I cannot make new steps for each and every job.
Hi Everyone, I am migrating my old sql server database to the new hardware, in the processes I migrated all the databases from old sql server 7.0(All tables from MSDB Database, sysjobs, syshistory etc) to new sql server 7.0, and also generated the scripts for all jobs and executed in the new server, all the jobs appear but when I go and see in the job steps, I don't see any steps in the jobs, I tried different ways but I am not able to get the steps. Can any one help me out.............. I have about 160 jobs so I cannot make new steps for each and every job.
