SQL 2012 - change server to mixed authentication now cannot see databases

Hi all,

Running SQL 2012 SP1. A database I just created requires SQL authentication so I changed the properties  to allow mixed authentication and rebooted. Now I cannot see any of my databases! can anyone help?!
MJB2011Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

geek_vjCommented:
Looks like the login you used to access the SQL Mgmt studio doesnt have access to view user databases. Are you trying to use sysadmin account - domain account or sa or any specific account?

Also, if u r using any specific login, do specify what level of access it is having at server and db level.
0
MJB2011Author Commented:
Im using my domain admin account. As before the reboot this could see all databases.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Do you see the SQL Server running in SQL Configuration manager?

Changing to mixed should not cause this to happen as Windows auth still is there. Did you give the sa user a password when you changed to mixed?

Can you use sa to login to the SQL Server?

There is a way to get back in, so no worries there, just want to see what you can see before we use the big hammer.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MJB2011Author Commented:
SQL is running but I can only see the basic system databases.

the sa account is currently granted permission to connect to databases but not to login.
0
MJB2011Author Commented:
I can see my databases files still in the folder directories.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Is your Windows User in the sysadmin role on the SQL Server?
0
MJB2011Author Commented:
yes it is
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
When you login, what results do you get from this query?

SELECT l.name as grantee_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON   p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;
0
MJB2011Author Commented:
Public - Grant - VIEW ANY DATABASE


Not sure whether I have mentioned that the databases that are missing are all currently offline.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Well, they should still have the database listed and a status of Offline.  But you don't see any databases listed under Databases in SSMS?
0
MJB2011Author Commented:
No Im afraid not. I only see 2 databases plus the default DB's that are stored in the location:

D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

However the databases that I have lost since rebooting are in the location:

D:\Program Files\Microsoft SQL Server\MSSQL11.SCCM\MSSQL\DATA


I am confused by this as im sure there wasnt another instance.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Well, the path that includes MSSQL11.SCCM indicates that there is a named instance called SCCM.

So you will want to connect to that instance to see the other databases.

localhost\SCCM if you are on that server trying to connect.
0
MJB2011Author Commented:
OK I have now looked in the services and I can see there is another instance of SQL which has failed to start. So apologies i didnt realise this. When looking in the event log there seems to be a port conflict with the 2 instances. Soon as I stopped the other instance ans started the other it worked. I can now see my other databases.

Why would this conflict?

Also would changing the SQL authentication to mixed cause problems?
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Well, you would need to look in the SQL Configuration manager for each instance and look at the Network Protocols.  I am not sure why changing to Mixed would cause a port problem, but there may have been a concept that the original instance was stopped and it was listening on 1433 and then the SCCM instance was started and listening on 1433 as well. There would not be a conflict as long as only one instance is started, but when you rebooted, both may have been set to startup Automatically and now only one can start because they are using the same IP on the machine and the same port, which won't work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geek_vjCommented:
Why would this conflict?
>> May be they might be using the same default port - 1433

Also would changing the SQL authentication to mixed cause problems?
>> Should be the cause. Both might have been started automatically at the same time and one will get succeeded and in that the databases would have been absent where as after stopping it and starting the other one, the original instance with databases might have come up and thus u were able to see the databases

To address the port issue, its better you change the port from SQL configuration manager for one instance so that they doesnt conflict again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.