Cant connect to existing MS SQL Server 2008

Question

Cant connect to existing MS SQL Server 2008

john.kerns

SSC Journeyman

Points: 86
More actions
November 15, 2010 at 10:06 am

#229320

I'm a programmer at a new company. I took over a machine with MSSQL Server 2008 already installed locally. I removed the account of the old user and created one for myself and have admin rights. I run SSMS as administrator and try to connect. I'm sure I have no account set up in the server for windows authentication and can't seem to connect using SQL Server Authentication using SA and empty password. I use the name of the computer for the Server Name. That said is there a way for me to set up an account i can use to connect to the database or do I need to uninstall and reinstall the server which I hope to avoid. Its a small company and as far as I can tell we have no DBA plus most the guys are out of town anyway. Appreciate any advice/feedback.
Thanks
John

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

I did not understand why you are trying to connect using SA with empty password. Back in the bad old days, everyone left the password of SA account blank. Did you install the SQL Server or did someone else install it for you? You should check with the person who installed it what is the Password for SA.

It would be wise to create windows authenticated accounts into your Local DB and then always connect using the Windows account.

-Roy

Derrick Smith SSChampion Points: 10833 More actions · Answer 2

If you don't have a SQL login with sysadmin available to you (don't know the SA password), and your domain account is not an admin..well you're a bit hosed then. You're basically looking at a reinstall.

Once you reinstall, grant sysadmin to your local administrators group..SQL 2005 did this by default but it was disabled in 2008. For a local instance, IMO, it should be there.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

One thing you can look at is to see under which account your SQL Service is running. If it is running under a windows account, you are in luck. You can login to the system with the service account and connect to the instance and add yourself as a sysadmin or reset your SA password.

-Roy

Derrick Smith SSChampion Points: 10833 More actions · Answer 4

Roy Ernest (11/15/2010)
One thing you can look at is to see under which account your SQL Service is running. If it is running under a windows account, you are in luck. You can login to the system with the service account and connect to the instance and add yourself as a sysadmin or reset your SA password.

Does that work? I could've sworn I tried that one time and I still couldn't connect under a similar circumstance..although this could've been with SQL 2000 since it was a long time ago.

Pravasis Default port Points: 1455 More actions · Answer 5

But, if you are an admin of the local system, then it should allow you to connect using the windows authentication!

Regards,
Pravasis

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

pravasis (11/16/2010)
But, if you are an admin of the local system, then it should allow you to connect using the windows authentication!

No it wont. In SQL 2008, Local admin or domain admin does not have a login in SQL Server 2008 by default. You have to add the user explicitly.

-Roy

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

Derrick Smith (11/15/2010)
Roy Ernest (11/15/2010)
One thing you can look at is to see under which account your SQL Service is running. If it is running under a windows account, you are in luck. You can login to the system with the service account and connect to the instance and add yourself as a sysadmin or reset your SA password.
Does that work? I could've sworn I tried that one time and I still couldn't connect under a similar circumstance..although this could've been with SQL 2000 since it was a long time ago.

If I am not mistaken, yes it will work. But you have to make sure that the Service account has the rights to login locally to the box.

-Roy

john.kerns SSC Journeyman Points: 86 More actions · Answer 8

Roy

"One thing you can look at is to see under which account your SQL Service is running. If it is running under a windows account, you are in luck. You can login to the system with the service account and connect to the instance and add yourself as a sysadmin or reset your SA password."

Looking in the services dialog. Where do I figure out if its running under a windows account?

The guy who installed this server has left the company a long time ago so I don't have his log in info. There is no admin account other then mine.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

Go to Services, Click on the SQL Server service, and take properties. You can see if it is running under domain account or local system.

-Roy

john.kerns SSC Journeyman Points: 86 More actions · Answer 10

local system radio button is selected. don't suppose I can select this account and put my account in.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 11

You can try to do it and then restart the system. It could wreck everything up but I do not see any other way you can get in unless you have your SA password.

-Roy

john.kerns SSC Journeyman Points: 86 More actions · Answer 12

Thanks for your help Roy. If it works I'll post it. Take care.

Pravasis Default port Points: 1455 More actions · Answer 13

Thanks Roy. I have mostly worked on 2005. So, I was thinking, its the same in 2008. Thanks for the clarification.

Regards,
Pravasis