MS SQL help?

[KaptKaos]

I have a SQL databases that I am moving to a development server. I have backed up and restored the databases, and can confirm that the data does reside in those database.

However, the Report Server is giving me fits. If I try to run reports, it asks for credentials, but I have Windows authentication enabled.

I am a bit of a noob when it comes to SQL DBA, so please write slowly. SQL 2008r2.

TIA

[LSA]

Is the windows account active directory or local? Check AD that it's still enabled? Can you log in using SA (sql server master account)?

Oh misread part of that, is the account you're using have permission to access the databases you are trying to generate reports from?

[KaptKaos]

Originally Posted by LSA

Is the windows account active directory or local? Check AD that it's still enabled? Can you log in using SA (sql server master account)?

Oh misread part of that, is the account you're using have permission to access the databases you are trying to generate reports from?

I am logged in as domain admin, on the SQL server which is in the domain.

I believe I have SA permissions, but would be helpful to know where to confirm that.

[stomachmonkey]

The user account that is submitting the report query is either not listed as a user for that specific DB or does not have the necessary permissions.

I would add the user (with appropriate rights) directly to the DB for testing of the migration then backtrack to find where permissions are not being passed.

[KaptKaos]

Originally Posted by stomachmonkey

The user account that is submitting the report query is either not listed as a user for that specific DB or does not have the necessary permissions.

I would add the user (with appropriate rights) directly to the DB for testing of the migration then backtrack to find where permissions are not being passed.

Checking that now.

How do I track where permissions are being passed?

[1-ev.com]

without knowing whole picture it is hard to help, such as:

What is the old DBs are, (Import-Export function could be used)

How many DBs and what is the new infrastructure will be?

Speaking of the just of the passwords, you need to make sure you have you have SQL rights on Domain level.

PM me or see my sig, if you have questions

Regards, -Y

[Shifter]

SQL doesn't carry the login credentials with the database when you restore to a different instance of SQL when using sql authentication. Even if the logins are the same cross platform, the hash tied to the login will be different between sql instances.

expand the database, then security and then users. Does the user accessing the database exist here? Go back to the root level and expand Security and then logins. Does the user exist here?

If the user exists in both locations, drop them from the database and readd them.

sp_dropuser username

sp_adduser username

[Shifter]

And to check your rights, under security, logins rightclick the username go to properties. Select server roles, you will see the rights you have on the sql server. Under user mapping you will see what databases the user has access to and what level of access you have.

[Shuie]

Not knowing what application, reporting front end (Crystal, etc.), I would look at the log files and/or check the connection string with the refreshed database. If you did something like copy the app server or report server config file over to your dev instance you could still be trying to connect to the source DB.

[KaptKaos]

It was a few issues.

First, a restore didn't include all of the stored procedures for a particular database for some reason.

Then, the reporting server connection string was buggered up.

I still have an issue with the reporting server not getting the AD credentials, but stuff is at least flowing in the right direction.

Thanks all for the help.

[Head416]

Read this: How to resolve permission issues when you move a database between servers that are running SQL Server

It's kind of like how a local user account on one PC isn't recognized by another PC.

[John Rogers]

I learned from the many years I was a DBA before retiring a few years ago that there are several things you can and should do make make the application front end and database work smoothly and safely. They include:

- Never use single sign on to manage the user connections to the database. Set them up inside the database and make sure the data in that user table is encrypted. This prevents a person that has gained access to the network from gaining access to the database of important information.

- If you have to copy the application and database to a new server for upgrades and testing, copy everything including the report engine, application, etc and do not rely on anything from the production side to do any testing with the new system. It is very easy to get the database, reports, apps, etc mixed up and bugger the production system.

- If possible, keep the application and database off the domain or in a separate small domain away from everything else. This prevents the possibility of the database getting hacked into easily.

- Minimize the number of users who can access the application and database servers, especially the production system. Generally we allowed the sysadmin, backup sysadmin, DBA and backup DBA (when I had one) to actually be able to connect to the server box directly. You can see the reason why I would guess.

- When making a copy of either the application or database, use a "diff" program like Oracle has so you can verify everything has been moved and not cause problems if pieces of the database or application are missing. This can be minimized if the test server(s) have similar naming for directories and other things.

Generally these things are done when designing the overall system and I teach this in one of the database/business systems design classes but we use Oracle as the database but these rules can work with any database and any front end.

[Head416]

Sorry, that linked has some good info, but this is what you should lookup:

sp_change_users_login 'Auto_Fix', 'user'

That should fix an orphaned account with the login ID in place of 'user'.

[1-ev.com]

this is real live scenario I've seen in my practice a few times...

Originally Posted by Head416

Read this: How to resolve permission issues when you move a database between servers that are running SQL Server

It's kind of like how a local user account on one PC isn't recognized by another PC.

[id10t]

id10t@box:~/ $ mysqldump -u dbuser -p database | gzip -c > databasename.sql.gz
id10t@box:~/ $ scp databasename.sq.gz user@newhost:~/
id10t@box:~/ $ ssh -i .ssh/identity.pem user@newhost 
user@newhost:~/ $ mysql -u root -p
Enter password:
mysql> create database devdb;
mysql> grant ALL on devdb to 'dbuser@localhost' identified by 'secretpassword';
mysql> quit

user@newhost:~/ $ mysql -u dbuser < zcat databasename.sql.gz

Makes me glad I didn't have the $$ for those MCSE classes back in '98....

[Shuie]

Makes me glad I'm a functional working with object based cloud solutions now instead of relational DBs