Can't connect using sa login and don't have any other administrator logins

I connected successfully in SQL Server Management studio using the 'sa' login and changed the authentication mode to Windows NT because I could not connect using my windows login but now I can't get any connection at all because 'sa' is no longer trusted.

I changed the loginmode registry setting to 2 and rebooted my PC but this has made no difference.

Can anyone help me get the 'sa' login back?

Unless you explicitly removed the default privileges (members of sysadmin) to builtin\Administrators, you should be able to connect to SQL Server using a Windows account that is member of the machine administrator s group.

NOTE: If you are using Windows Vista, you probably have UAC enabled, and you will need to use a full administrator token before connecting to SQL Server in the way I described above. You can use the right-click menu in Windows to “Run as Administrator” in order to get an unrestricted token before connecting to SQL Server.

Let us know if this information helped.

-Raul Garcia

SDE/T

SQL Server Engine

|||

I am on Windows XP, the engine is SQL Server 2000 and I am using SQL Server 2005 Management Studio. The problem started when I upgraded the software we are developing to the latest version which I suspect may have removed the existing SQL Server logins which did have full rights. It also changed the login mode from Windows Authentication to System Administration. My big mistake was to change the mode back to Windows Authentication in SQL Server Management Studio before checking the other logins and it was then that the problems started. My windows username has administrative rights but I still couldn't connect to the server using windows login.

I tried just now changing the LoginMode in the registry back to 1 and starting windows again but I still can't log in with 'sa'. I did notice that the default user registry key is set to 'guest' but don't know if that makes any difference.

I think that I may have to resort to re-installing SQL Server 2005. I would prefer not to do that but It doesn't really matter if I lose my local database as I do have a copy of it elsewhere.

Can you think of any other solutions?

|||

Try switching the LoginMode value in the registry to 2 (if I remember correctly, 2 should mean "Mixed mode"), restart SQL Server and try to connect with the SA login again.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks, that worked perfectly.

FYI...

The reason it didn't work the first time is that I changed it in registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer\LoginMode

which I assume is the key for SQL Server Management Studio 2005 but I didn't realise that there was another key, i.e.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode

which would be for the SQL Server 2000 engine.

Anyway, I have learned my lesson now and am going to add my windows login.

Can't connect to SQL Server 2005 Beta 2

I cannot connect to our SQL Server 2005 Beta 2 database server from my
machine. This is the error I get:
"A connection was successfully established with the server, but then an
error occurred during the login process. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.) (.Net
SqlClient Data Provider)"
Doesn't matter if I'm using Windows Authentication or Sql Server
Authentication.
I'm trying to connect to it with the April CTP management studio and
Business Intelligence Dev Studio.
Thanks,
Jason
Have you added your login to the 2005 server? Sorry for the low-level
question....
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"JasonP" <JasonP@.discussions.microsoft.com> wrote in message
news:AAF608B1-0CD3-4B7E-ABA9-B5CA0D058C6C@.microsoft.com...
> I cannot connect to our SQL Server 2005 Beta 2 database server from my
> machine. This is the error I get:
> "A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: TCP Provider, error:
> 0 -
> An existing connection was forcibly closed by the remote host.) (.Net
> SqlClient Data Provider)"
> Doesn't matter if I'm using Windows Authentication or Sql Server
> Authentication.
> I'm trying to connect to it with the April CTP management studio and
> Business Intelligence Dev Studio.
> Thanks,
> Jason
|||Yes, I created specific users on that server other than sa to see if that
was the problem.
However, I have more information. The database I'm trying to access is a
Sql Server 2005 Beta 2 on Windows Server 2003. If I try to access the server
through another Sql Server Beta 2 client I can login using Sql Server
Authentication.
The machine I'm trying to access it with now currently has the latest Sql
Server 2005 April CTP with VS 2005 Beta 2 on it and cannot access the Beta 2
server.
Has anyone had any problems like this with the April CTP?
Thanks,
Jason
"Kevin3NF" wrote:

> Have you added your login to the 2005 server? Sorry for the low-level
> question....
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "JasonP" <JasonP@.discussions.microsoft.com> wrote in message
> news:AAF608B1-0CD3-4B7E-ABA9-B5CA0D058C6C@.microsoft.com...
>
>
|||Kevin;
I just installed SQL Server 2005 Beta 2 in a virtual pc running Windows
Server 2003 Enterprise Edition, also installed VS 2005 Beta 2 on an XP SP2
machine and can't connect to the SQL Server. I am getting the same error as
you.
Let me know if you got a fix for this.
Thanks,
M. Castellanos
"JasonP" wrote:
[vbcol=seagreen]
> Yes, I created specific users on that server other than sa to see if that
> was the problem.
> However, I have more information. The database I'm trying to access is a
> Sql Server 2005 Beta 2 on Windows Server 2003. If I try to access the server
> through another Sql Server Beta 2 client I can login using Sql Server
> Authentication.
> The machine I'm trying to access it with now currently has the latest Sql
> Server 2005 April CTP with VS 2005 Beta 2 on it and cannot access the Beta 2
> server.
> Has anyone had any problems like this with the April CTP?
> Thanks,
> Jason
>
> "Kevin3NF" wrote:
|||Beta 2 has expired. Since 7/1/2005, you won't be able to connect to it from
a machine that has another framework active. You can still use the tools on
the server itself, but you won't be able to use Help.
You will need to uninstall Beta 2 and install the CTP. This is a big
process which I am struggling with right now. I have been advised that the
entire server should be wiped and rebuilt from scratch. GRRR...
Graeme Martin
"MC" wrote:
[vbcol=seagreen]
> Kevin;
> I just installed SQL Server 2005 Beta 2 in a virtual pc running Windows
> Server 2003 Enterprise Edition, also installed VS 2005 Beta 2 on an XP SP2
> machine and can't connect to the SQL Server. I am getting the same error as
> you.
> Let me know if you got a fix for this.
> Thanks,
> --
> M. Castellanos
>
> "JasonP" wrote:
|||Thanks for the info.
M. Castellanos
"The Oracle" wrote:
[vbcol=seagreen]
> Beta 2 has expired. Since 7/1/2005, you won't be able to connect to it from
> a machine that has another framework active. You can still use the tools on
> the server itself, but you won't be able to use Help.
> You will need to uninstall Beta 2 and install the CTP. This is a big
> process which I am struggling with right now. I have been advised that the
> entire server should be wiped and rebuilt from scratch. GRRR...
> --
> Graeme Martin
>
> "MC" wrote:
|||OK, the process wasn't so bad, and I didn't need to wipe the server. But
there are few helps and 'gotchas' to be aware of.
1. Be sure to uninstall in the correct order. The last two SS items are the
Tools and the Native Client. Last of all is the Framework. Supposedly, you
can just run step 2 (below), and it will preserve many of your preferences.
I wanted a clean uninstall, so I went through Add/Remove Programs and
followed the steps.
2. The CTP has a 'Setup Tools' directory. Run the sqlbuw.exe located in the
Build Uninstall Wizard subdirectory after you have uninstalled everything.
This should clean up any remaining stuff.
3. After you install CTP, check the following info in another post I
recently made:
http://www.microsoft.com/communities...261&sloc=en-us
Good luck! SQL Server is a great product and this new version is worth the
effort.
Graeme Martin
"MC" wrote:
[vbcol=seagreen]
> Thanks for the info.
> --
> M. Castellanos
>
> "The Oracle" wrote:

Can't connect to SQL Server 2005 Beta 2

I cannot connect to our SQL Server 2005 Beta 2 database server from my
machine. This is the error I get:
"A connection was successfully established with the server, but then an
error occurred during the login process. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.) (.Net
SqlClient Data Provider)"
Doesn't matter if I'm using Windows Authentication or Sql Server
Authentication.
I'm trying to connect to it with the April CTP management studio and
Business Intelligence Dev Studio.
Thanks,
JasonHave you added your login to the 2005 server? Sorry for the low-level
question....
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"JasonP" <JasonP@.discussions.microsoft.com> wrote in message
news:AAF608B1-0CD3-4B7E-ABA9-B5CA0D058C6C@.microsoft.com...
> I cannot connect to our SQL Server 2005 Beta 2 database server from my
> machine. This is the error I get:
> "A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: TCP Provider, error:
> 0 -
> An existing connection was forcibly closed by the remote host.) (.Net
> SqlClient Data Provider)"
> Doesn't matter if I'm using Windows Authentication or Sql Server
> Authentication.
> I'm trying to connect to it with the April CTP management studio and
> Business Intelligence Dev Studio.
> Thanks,
> Jason|||Yes, I created specific users on that server other than sa to see if that
was the problem.
However, I have more information. The database I'm trying to access is a
Sql Server 2005 Beta 2 on Windows Server 2003. If I try to access the serve
r
through another Sql Server Beta 2 client I can login using Sql Server
Authentication.
The machine I'm trying to access it with now currently has the latest Sql
Server 2005 April CTP with VS 2005 Beta 2 on it and cannot access the Beta 2
server.
Has anyone had any problems like this with the April CTP?
Thanks,
Jason
"Kevin3NF" wrote:

> Have you added your login to the 2005 server? Sorry for the low-level
> question....
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "JasonP" <JasonP@.discussions.microsoft.com> wrote in message
> news:AAF608B1-0CD3-4B7E-ABA9-B5CA0D058C6C@.microsoft.com...
>
>|||Jason,
Did you find a solution to your problem then? I am having the same issues,
and looking for an answer.
Could you maybe post your solution, please?
Thanks
Rigalo|||Kevin;
I just installed SQL Server 2005 Beta 2 in a virtual pc running Windows
Server 2003 Enterprise Edition, also installed VS 2005 Beta 2 on an XP SP2
machine and can't connect to the SQL Server. I am getting the same error as
you.
Let me know if you got a fix for this.
Thanks,
M. Castellanos
"JasonP" wrote:
[vbcol=seagreen]
> Yes, I created specific users on that server other than sa to see if that
> was the problem.
> However, I have more information. The database I'm trying to access is a
> Sql Server 2005 Beta 2 on Windows Server 2003. If I try to access the ser
ver
> through another Sql Server Beta 2 client I can login using Sql Server
> Authentication.
> The machine I'm trying to access it with now currently has the latest Sql
> Server 2005 April CTP with VS 2005 Beta 2 on it and cannot access the Beta
2
> server.
> Has anyone had any problems like this with the April CTP?
> Thanks,
> Jason
>
> "Kevin3NF" wrote:
>|||Beta 2 has expired. Since 7/1/2005, you won't be able to connect to it from
a machine that has another framework active. You can still use the tools on
the server itself, but you won't be able to use Help.
You will need to uninstall Beta 2 and install the CTP. This is a big
process which I am struggling with right now. I have been advised that the
entire server should be wiped and rebuilt from scratch. GRRR...
Graeme Martin
"MC" wrote:
[vbcol=seagreen]
> Kevin;
> I just installed SQL Server 2005 Beta 2 in a virtual pc running Windows
> Server 2003 Enterprise Edition, also installed VS 2005 Beta 2 on an XP SP2
> machine and can't connect to the SQL Server. I am getting the same error a
s
> you.
> Let me know if you got a fix for this.
> Thanks,
> --
> M. Castellanos
>
> "JasonP" wrote:
>|||Thanks for the info.
M. Castellanos
"The Oracle" wrote:
[vbcol=seagreen]
> Beta 2 has expired. Since 7/1/2005, you won't be able to connect to it fr
om
> a machine that has another framework active. You can still use the tools
on
> the server itself, but you won't be able to use Help.
> You will need to uninstall Beta 2 and install the CTP. This is a big
> process which I am struggling with right now. I have been advised that th
e
> entire server should be wiped and rebuilt from scratch. GRRR...
> --
> Graeme Martin
>
> "MC" wrote:
>|||OK, the process wasn't so bad, and I didn't need to wipe the server. But
there are few helps and 'gotchas' to be aware of.
1. Be sure to uninstall in the correct order. The last two SS items are the
Tools and the Native Client. Last of all is the Framework. Supposedly, you
can just run step 2 (below), and it will preserve many of your preferences.
I wanted a clean uninstall, so I went through Add/Remove Programs and
followed the steps.
2. The CTP has a 'Setup Tools' directory. Run the sqlbuw.exe located in the
Build Uninstall Wizard subdirectory after you have uninstalled everything.
This should clean up any remaining stuff.
3. After you install CTP, check the following info in another post I
recently made:
http://www.microsoft.com/communitie...3261&sloc=en-us
Good luck! SQL Server is a great product and this new version is worth the
effort.
--
Graeme Martin
"MC" wrote:
[vbcol=seagreen]
> Thanks for the info.
> --
> M. Castellanos
>
> "The Oracle" wrote:
>

Can't connect to Report Server Model

I've created and successfully deployed a Report Server Model. However, when I try to use it as a data source to write reports against, I can't connect to it. When I create a new shared data source, the option of Report Server Model appears in the Type: dropdown, but there's no way for me to add/edit the connection string. That option is grayed out.

Is this a security issue? I've checked all the permission options in Management Studio and I should have access to everything. I'm not sure what he problem is here.

Any help is appreciated.

BTW, I can create reports using Report Builder (web interface), but not in BIDS.

Anyone?

Can't connect to named instance from clients

I created a second SQL Server 2000 instance in my computer. I started the
service using the service manager and registered it successfully to the
local Enterprise Manager.
When I try to register the instance to a remote Enterprise Manager or when I
try to create a dsn from another machine to the new instance I get a "SQL
Server is unavailable or does not exist" error.
Sergio Florez M.
Miembro activo www.AlianzaDev.net
El que persevera insiste
Medelln, Colombia
DCE 4
How are you connecting to the instance?
Servername\InstanceName?
"Sergio Florez M." wrote:

> I created a second SQL Server 2000 instance in my computer. I started the
> service using the service manager and registered it successfully to the
> local Enterprise Manager.
> When I try to register the instance to a remote Enterprise Manager or when I
> try to create a dsn from another machine to the new instance I get a "SQL
> Server is unavailable or does not exist" error.
> --
> Sergio Florez M.
> Miembro activo www.AlianzaDev.net
> El que persevera insiste
> MedellXn, Colombia
> DCE 4
>
>

Can't connect to named instance from clients

I created a second SQL Server 2000 instance in my computer. I started the
service using the service manager and registered it successfully to the
local Enterprise Manager.
When I try to register the instance to a remote Enterprise Manager or when I
try to create a dsn from another machine to the new instance I get a "SQL
Server is unavailable or does not exist" error.
Sergio Florez M.
Miembro activo www.AlianzaDev.net
El que persevera insiste
Medelln, Colombia
DCE 4How are you connecting to the instance?
Servername\InstanceName?
"Sergio Florez M." wrote:

> I created a second SQL Server 2000 instance in my computer. I started the
> service using the service manager and registered it successfully to the
> local Enterprise Manager.
> When I try to register the instance to a remote Enterprise Manager or when
I
> try to create a dsn from another machine to the new instance I get a "SQL
> Server is unavailable or does not exist" error.
> --
> Sergio Florez M.
> Miembro activo www.AlianzaDev.net
> El que persevera insiste
> Medell_n, Colombia
> DCE 4
>
>