Changing computer name

Can anyone advise how to change the computer name of a PC
which is running sql client with msde.
I find if I change the computer name sql server will not
start.
Any advise would be appreciated. Thanks
The following works for the default instance:
DECLARE @.old_server_name SYSNAME
DECLARE @.new_server_name SYSNAME
SET @.old_server_name = @.@.SERVERNAME
SET @.new_server_name = <your new computer name>
EXEC sp_dropserver @.server = @.old_server_name
EXEC sp_addserver @.server = @.new_server_name , @.local = 'LOCAL'
UPDATE msdb..sysjobs
SET originating_server = @.new_server_name
WHERE originating_server = @.old_server_name
You have to restart SQL Server (MSDE), and then change the computername, for
it to take effect though.
Jacco Schalkwijk
SQL Server MVP
"Elayne" <pstjw@.hotmail.com> wrote in message
news:c37501c47a33$c0d79090$a401280a@.phx.gbl...
> Can anyone advise how to change the computer name of a PC
> which is running sql client with msde.
> I find if I change the computer name sql server will not
> start.
> Any advise would be appreciated. Thanks
|||I've never experienced the SQL Server service failing to start after the
computer name was changed. What is the error you get when starting the
service from the command line (net start mssqlserver)? Are you able to
start SQL Server as console app by running "sqlservr.exe -c" from the
command line?
Regards,
Farooq Mahmud [MS SQL Support]
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Changing ASP Application to use SQL Native Client

I am attempting to change an ASP application we've got to use SQL Native Client instead of MDAC and running into a problem.

If I use ODBC driver our application runs without problem, but if I try to use the OLEDB provider certain pages are throwing the following error: Item cannot be found in the collection corresponding to the requested name or ordinal.

The pages that are not working are using ADODB.Command object to execute a Stored Procedure and populate ADODB.Recordset object. The error is thrown when I try to access a field (by name or index) in the recordset. I am using recordsetname(0) or recordsetname("fieldname") to access the data in the recordset.

I have other pages that are working that use this same technique, so I must be missing something else that is different about the two ASP's.

Has anyone else run into this problem switching to the OLEDB provider?

Thanks,
Kelsey

Are you accessing columns in any of the new SQL Server 2005 data types? With ADO you must use DataTypeCompatibility=80 in your connection string. For example

Dim con As New ADODB.Connection

con.ConnectionString = "Provider=SQLNCLI;" _
& "Server=(local);" _
& "Database=AdventureWorks;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
con.Open

See http://msdn2.microsoft.com/en-us/library/ms130978.aspx for more details about using SQL Native Client from ADO applications

If this doesn't help please send more details (preferably source code)

|||

Hi Chris -

We are not accessing any of the new SQL Server 2005 data types yet. We do have DataTypeCompatibility=80 in the connection string. Here is an example of the code that produces the error. "oursp" is a stored procedure that has "url" as a field in the resultset. When I run this page I get the "item can not be found in collection" error. If I change this code to use "Driver={SQL Native Client}" instead of the "Provider=SQLNCLI;" it works without any problem.

Any ideas?

Thanks,
Kelsey

--

<%
'Dim some local variables.
Dim lid, llist, rsGen, Cn, p, cmd

'Create Recordset Object
Set rsGen = Server.CreateObject("ADODB.Recordset")

'Set local variables for Stored Procedure Call
lid = 3
llist = "36,37,38"

'Create Connection Object
Set Cn = Server.CreateObject ("ADODB.Connection")

'Set Timeouts
Cn.ConnectionTimeout = 250
Cn.CommandTimeout = 250

'Open connection with SQL Native Client Provider
Cn.Open "Provider=SQLNCLI;"_
&"DataTypeCompatibility=80;"_
&"MultipleActiveResultSets=false;"_
&"Connection Reset=false;"_
&"Server=ourserver;"_
&"Database=ourdb;"_
&"UID=ouruser;"_
&"PWD=ourpass;"

'Create Command Object
set cmd = server.CreateObject("ADODB.Command")

'Set active connection
cmd.activeconnection = Cn

'Set command text to Stored Procedure name
'and command type to stored procedure
cmd.Commandtext = "oursp"
cmd.Commandtype = 4

'Create Stored Procedure parameters
set p = cmd.Parameters
p.Append cmd.CreateParameter("@.id", 3, 1)
p.Append cmd.CreateParameter("@.list", 200, 1, 255)

cmd("@.id") = lid
cmd("@.list") = llist

'Create the recordset with results of Stored Procedure call
set rsGen = cmd.Execute

'Trap and display any errors thrown when
'trying to access field in recordset
ON ERROR RESUME NEXT

response.write rsGen("url")

If Err.number <> 0 then
response.write Err.Number&"-"&Err.description
Cn.Close
Set Cn = Nothing
response.end
End If

cn.Close
Set cn = Nothing
%>

|||

So this works when you use SQL Native Client ODBC via Kagera (aka MSDASQL the OLE DB to OBC bride which is now deprecated) - which isn't a supported scenario (see http://msdn2.microsoft.com/en-us/library/ms131035.aspx) but not when you use SQL Native Client OLE DB directly (which is supported).

The next step for me is to know if MDAC OLE DB worked as you expected or if this has only ever worked via Kagera.

One possibility is that your stored procedure is generating multiple results and this is handled differently in the two cases. If this is so, then executing

set rsGen = rsGen.NextRecordset

until you settle on the right result set would solve the problem

It might also be that for some reason no rows are being returned, so a check for BOF to detect this would help.

If you call the stored proc from SQL Management Studio you should be able to see if multiple results are being returned.

I don't think I can get much further without knowing if SQLOLEDB works OK, knowing that a row is definitely being returned and knowing more about what the stored procedure does.

|||

You are correct... there was multiple resultsets coming back from the Stored Procedure call. Doing the rsGen.NextRecordset made the error go away. When I execute the Stored Procedure in SMS it looks like a count is being returned first, then the actual result set. If I set NOCOUNT on in SMS it goes away. Is there a way I can set NOCOUNT on in my connection string, or is this a database setting I need to change?

Thank you very much for the help.

Kelsey

|||

Hi Kelsey,

When SQL Server executes a stored procedure it will send a row count to the client for each non-query statement executed. Setting NOCOUNT ON stop this.

Connection string keywords for SQL NAtive Client are here http://msdn2.microsoft.com/en-us/library/ms130822.aspx

You can't set NOCOUNT ON via the connection string, but you can execute the statement 'SET NOCOUNT ON' on the connection before calling the stored procedure. If you execute the SET statement in the stored procedure itself, its effect will be limited to execution of the stored procedure. See http://msdn2.microsoft.com/en-us/library/ms189837.aspx for reference

|||

Hi Chris,

Thanks for the info. The only thing that is hanging me up now is the fact that we have 3000 stored procedures and many more ASP's calling them. The connection string is in an include, but I don't want to call "SET NOCOUNT ON" for each connection open (page load). I could probably justify spending the time required to go through and add it to each of our Stored Procedures if there was going to be performance gain and/or would enable us to use the latest technology available.

Do you know why SQL Native Client OLE DB treats the count as a result set when MDAC and even SQL Native Client ODBC do not? Is it possible that SQL Native Client OLE DB will be updated to function the same way in the future preventing me from needing to go through our Stored Procedures and adding "SET NOCOUNT ON"?

The main reason we are trying to upgrade is that we've noticed through Profiler Traces that using SQL Native Client OLE DB (or ODBC for that matter) eliminates a large number of sp_cursor type calls that are occuring when our application is using MDAC. With SQL Native Client we've noticed that these calls are handled with a single sp_executesql instead of the many sp_cursor calls.

Thanks again for your time and help.

Kelsey

|||

Hi Kelsey,

I'd like to investigate this further, could you send me sample code that is representative of one of your stored procs that is giving problems and how it is called. If you can do this in VB6 that would be even better without being in an ASP page. If not just code fragments will be enough to get me started.

If you're not seeing sp_cursor calls where you were before, then this indicates that a default result set is being returned where previously a server cursor was used. This would be consistent with the stored proc not being eligable for use with a server cursor (to be used with a server cursor a proc has to be a single select statement or a call to another proc that is a single select statement). If a proc can't be used with a server cursor, then it may return one or more counts/default result sets.

I'd expect OLE DB in MDAC and SQL Native Client to behave consistently, and ODBC in MDAC and SQL Native Client to behave consistently with regard to handling of results. ODBC and OLE DB may not necessarily behave the same though. To get a complete picture I'll need to compare each API running the same proc on both SQL Server 2000 and SQL Server 2005. Are you comparing MDAC to SQL Native Client with the same version of SQL Server?

|||

Chris,

It appears like the sp_cursor calls are not happening in places where we are executing Stored Procedures, but rather only in places where we are opening multiple AD-HOC Queries on the ASP. Here is a sample I have created that uses the PUBS DB. If run this page using old MDAC way the second AD-HOC query on the page is opened using a cursor. If I run this page using SQL Native Client (ODBC or OLEDB) it uses sp_executesql for the second AD-HOC instead of sp_cursoropen.

I have also noticed that if I change the first AD-HOC query to open the recordset using "rsGen.Open CMD.Execute" instead of "Set rsGen = cmd.Execute" the second AD-HOC will no longer be opened using sp_cursoropen.

I'm not sure if this behavior is directly related to the problem I am having with some Stored Procedures returning the count as a seperate resultset and others not, or if it is completely seperate.

I've got a large number of ASP's that have multiple AD-HOCs on them so I really would like to switch to SQL Native Client to prevent these cursor calls from happening.

Here is the sample code for recreating the multi AD-HOC problem. I am going to see if I can work up a seperate sample that shows the Stored Procedures that are returning count as a resultset and those that are not. I will post later on today.

Thanks for all the help.

Kelsey

--

<%
'Dim some local variables.
Dim rsGen, Cn, p, cmd

Const adCmdText = &H0001
Const adOpenForwardOnly = 0
Const adUseServer = 2

'Create Recordset Object
Set rsGen = Server.CreateObject("ADODB.Recordset")

'Create Connection Object
Set Cn = Server.CreateObject ("ADODB.Connection")

'Set Timeouts
Cn.ConnectionTimeout = 250
Cn.CommandTimeout = 250

'--
'New SQL Native Client way of connecting
'--
'Open connection with SQL Native Client Provider
'Driver={SQL Native Client}

Cn.Open "Provider=SQLNCLI;"_
&"DataTypeCompatibility=80;"_
&"MultipleActiveResultSets=false;"_
&"Connection Reset=false;"_
&"Server=SPILLWAY\DEV;"_
&"Database=pubs;"_
&"UID=test_user;"_
&"MARS Connection=False;"_
&"PWD=test;"
'--

'--
'Old MDAC Way of connecting
'using System DSN
'--
'Cn.Open "pubs", "test_user", "test"
'--

DIM lzip, lfname
lzip = 84152
lfname = "Gary"

'Create Command Object
set cmd = server.CreateObject("ADODB.Command")

'Set active connection
cmd.activeconnection = Cn

'
'Test AD HOC Query CAll #1
'
qryGen = "SELECT lname, fname FROM employee WHERE fname = ?"

cmd.CommandText = qryGen
cmd.ActiveConnection = CN
cmd.CommandType = adcmdtext
cmd.Parameters.Append cmd.CreateParameter("lfname", 202, 1, 20)
cmd("lfname") = lfname
rsGen.CursorType = adOpenForwardOnly
rsGen.CursorLocation = adUseServer
Set rsGen = cmd.Execute
'rsGen.Open cmd.Execute
response.write rsGen("lname")

rsGen.Close

'
'Test AD HOC Query Call #2
'
qryGen = "SELECT au_lname, au_fname FROM authors WHERE zip = ?"

set cmd = server.CreateObject("ADODB.Command")

cmd.CommandText = qryGen
cmd.ActiveConnection = CN
cmd.CommandType = adcmdtext
cmd.Parameters.Append cmd.CreateParameter("lzip", 129, 1, 5)
cmd("lzip") = lzip
rsGen.CursorType = adOpenForwardOnly
rsGen.CursorLocation = adUseServer
set rsGen = cmd.Execute
'

ON ERROR RESUME NEXT

if not rsGen.EOF then
response.write rsGen("au_lname")
end if

If Err.number <> 0 then
response.write Err.Number&"-"&Err.description
Cn.Close
Set Cn = Nothing
response.end
End If

cn.Close
Set cn = Nothing
%>

|||Thanks for this Kelsey. Sample code for a stored procedure would really help to understand what's happening. I don't know of a reason why SQL Server 2005 and SQL Server 2000 should behave differently in the way you are seeing, nor why ODBC and OLE DB should behave differently, so i'd really like to reproduce your issue.|||

Hi Kelsey,

In your include, where you configure the conection, you can add this:

oConn.Execute "Set NOCOUNT ON"

You don't need change all stored. In my case it works!

Regards

Jaime

PD: This post help me to be in the right way, thanks Chris and Kelsey

|||

Hi Jamie,

I had tried this as well.

What I didn't like about this solution is that "SET NOCOUNT ON" is good for the entire DB Session, so it seemed like this would create a situation where it was being called more times then nessecary.

I am not sure exactly how the scope of a DB Session is determined, so it is possible that it doesn't last beyond the life of single page load anyway, but I wasn't sure.

Perhaps Chris could shed some more light on this.

I'm still trying to fight my way back to sample stored procedures... just haven't had the time yet.

Thanks,
Kelsey

|||

SET NOCOUNT ON will last until the connection is closed, or you execute a SET NOCOUNT OFF

I tried your earlier sample code with MDAC and SQL Native Client against SQL Server 2000 and SQL Server 2005 using OLE DB and ODBC. MDAC + ODBC uses a server cursor for the second query, in all other cases you get a default result set. A default result set will generally give the best performance, so this is what you want in most cases. I don't see much point in exploring MDAC+ODBC further since OLE DB is the preferred choice for ADO (and ODBC+ADO isn't a supported scenario for SQL Native Client, since MSDASQL is now deprecated).

Changing a BIT to an INT where there's a CONSTRAINT and a DEFAULT

A few weeks ago a client asked me to add a column to a table so I
created this script:

ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0

Now they want to change it from a BIT to an INT, to store an enum.
Fair enough. However, no matter how much I wrangle with a script, I
can't find a reliable way to alter the column. I've mixed and matched
the following and nothing seems to work:

EXEC sp_unbindefault 'tblIndividual.fldRenewalStatus'

DROP DEFAULT DF_tblIndividual_fldRenewalStatus

ALTER TABLE tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default

ALTER TABLE tblIndividual
DROP COLUMN fldRenewalStatus
GO

ALTER TABLE tblIndividual
ADD fldRenewalStatus int NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0

Thoughts?

Thanks

EdwardALTER the table to add a new INT column. Set the value of that column
based on the data in the BIT column. Alter the table to drop the BIT
column. Rename the INT column.

Roy Harvey
Beacon Falls, CT

On 2 Jan 2007 09:19:00 -0800, teddysnips@.hotmail.com wrote:

Quote:

Originally Posted by

>A few weeks ago a client asked me to add a column to a table so I
>created this script:
>
>ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
>CONSTRAINT fldRenewalStatus_Default DEFAULT 0
>
>Now they want to change it from a BIT to an INT, to store an enum.
>Fair enough. However, no matter how much I wrangle with a script, I
>can't find a reliable way to alter the column. I've mixed and matched
>the following and nothing seems to work:
>
>EXEC sp_unbindefault 'tblIndividual.fldRenewalStatus'
>
>DROP DEFAULT DF_tblIndividual_fldRenewalStatus
>
>ALTER TABLE tblIndividual
>DROP CONSTRAINT fldRenewalStatus_Default
>
>ALTER TABLE tblIndividual
>DROP COLUMN fldRenewalStatus
>GO
>
>ALTER TABLE tblIndividual
>ADD fldRenewalStatus int NOT NULL
>CONSTRAINT fldRenewalStatus_Default DEFAULT 0
>
>
>Thoughts?
>
>Thanks
>
>Edward

|||On 2 Jan 2007 09:19:00 -0800, teddysnips@.hotmail.com wrote:

Quote:

Originally Posted by

>A few weeks ago a client asked me to add a column to a table so I
>created this script:
>
>ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
>CONSTRAINT fldRenewalStatus_Default DEFAULT 0
>
>Now they want to change it from a BIT to an INT, to store an enum.
>Fair enough. However, no matter how much I wrangle with a script, I
>can't find a reliable way to alter the column. I've mixed and matched
>the following and nothing seems to work:

Hi Edward,

You can do as Roy suggests, or you can run the following script:

ALTER TABLE dbo.tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default;

ALTER TABLE dbo.tblIndividual
ALTER COLUMN fldRenewalStatus INT NOT NULL;

ALTER TABLE dbo.tblIndividual
ADD CONSTRAINT fldRenewalStatus_Default DEFAULT 0 FOR fldRenewalStatus;

Running the ALTER COLUMN might take long if you have lots of data!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 03 Jan 2007 00:06:18 +0100, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

>You can do as Roy suggests, or you can run the following script:

Much simpler, of course. Somehow I had the idea that bit would not
convert to int. Thanks for the diplomatic correction. 8-)

Roy|||Hugo Kornelis wrote:

Quote:

Originally Posted by

On 2 Jan 2007 09:19:00 -0800, teddysnips@.hotmail.com wrote:
>

Quote:

Originally Posted by

A few weeks ago a client asked me to add a column to a table so I
created this script:

ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0

Now they want to change it from a BIT to an INT, to store an enum.
Fair enough. However, no matter how much I wrangle with a script, I
can't find a reliable way to alter the column. I've mixed and matched
the following and nothing seems to work:

>
Hi Edward,
>
You can do as Roy suggests, or you can run the following script:
>
ALTER TABLE dbo.tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default;
>
ALTER TABLE dbo.tblIndividual
ALTER COLUMN fldRenewalStatus INT NOT NULL;
>
ALTER TABLE dbo.tblIndividual
ADD CONSTRAINT fldRenewalStatus_Default DEFAULT 0 FOR fldRenewalStatus;
>
Running the ALTER COLUMN might take long if you have lots of data!

Thanks Hugo - that worked a treat!

Edward

Changes to SQL Client Tools

Hi
Does anyone know what are the changes maded to SQL Server
Client tools in SQL SP3.
DB Kid
Usually service packs don't do much to the client tools. Is there something
in particular you are having troubles with?
Andrew J. Kelly SQL MVP
"DB Kid" <anonymous@.discussions.microsoft.com> wrote in message
news:565901c480c7$45f703c0$a601280a@.phx.gbl...
> Hi
> Does anyone know what are the changes maded to SQL Server
> Client tools in SQL SP3.
> DB Kid
>

Changed sa password - can't connect via Enterprise Manager now

A client of mine changed his SA password "because it hadn't been changed in
a
while". Now nobody can get into the server via Enterprise Manager trying to
Connect on the server yields an error:
A connection could not be established to SERVER1.
Reason: login failed for user 'sa'.
Please verify SQL Server is running and check your SQL Server Registration
properties (by right-clicking on the SERVER1 node) and try again.
Of course, if I right-click on the SERVER1 node, I get the same message.
My client remembers the password he set; I managed to use it to get into
Query Analyzer, which prompts for a login each time it opens. But no luck
with Enterprise Manager. It's just trying to log in as sa without prompting
for a pw.
What can I do?
I already tried running sp_Password to change the pw back to what it was.
Still can't connect to the SQL Server via Enterprise Manager.Change the server registration to use Windows Auth instead of SQL Auth?
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Before connecting to server1 in EM, right click on it and edit the entry to
use the new password.
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed i
n a
> while". Now nobody can get into the server via Enterprise Manager trying t
o
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without promptin
g
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||In SQLEM, right-click on the server registration, and Edit settings. In the
dialog, change the login information.
You should probably be using Windows Authentication and if you can not
because you are remoting, create another login that only you know the
password to and use that to login. No one should ever use the sa account
unless there is an emergency. Each user should have their own SQL Server
login created, or only Windows Authenticated, which would give each user
their own login--unless, of cours, you share your network logins too.
Sincerely,
Anthony Thomas
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed i
n a
> while". Now nobody can get into the server via Enterprise Manager trying t
o
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without promptin
g
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Sorry, you said "remembers" and somehow I read "doesn't remember"...
You can right-click the server, "Edit SQL Server Registration Properties..."
and change the password there.
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.

Changed sa password - can't connect via Enterprise Manager now

A client of mine changed his SA password "because it hadn't been changed in a
while". Now nobody can get into the server via Enterprise Manager trying to
Connect on the server yields an error:
A connection could not be established to SERVER1.
Reason: login failed for user 'sa'.
Please verify SQL Server is running and check your SQL Server Registration
properties (by right-clicking on the SERVER1 node) and try again.
Of course, if I right-click on the SERVER1 node, I get the same message.
My client remembers the password he set; I managed to use it to get into
Query Analyzer, which prompts for a login each time it opens. But no luck
with Enterprise Manager. It's just trying to log in as sa without prompting
for a pw.
What can I do?
I already tried running sp_Password to change the pw back to what it was.
Still can't connect to the SQL Server via Enterprise Manager.Change the server registration to use Windows Auth instead of SQL Auth?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Before connecting to server1 in EM, right click on it and edit the entry to
use the new password.
"Norrick" wrote:
> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Sorry, you said "remembers" and somehow I read "doesn't remember"...
You can right-click the server, "Edit SQL Server Registration Properties..."
and change the password there.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||In SQLEM, right-click on the server registration, and Edit settings. In the
dialog, change the login information.
You should probably be using Windows Authentication and if you can not
because you are remoting, create another login that only you know the
password to and use that to login. No one should ever use the sa account
unless there is an emergency. Each user should have their own SQL Server
login created, or only Windows Authenticated, which would give each user
their own login--unless, of cours, you share your network logins too.
Sincerely,
Anthony Thomas
"Norrick" wrote:
> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.

Changed sa password - can't connect via Enterprise Manager now

A client of mine changed his SA password "because it hadn't been changed in a
while". Now nobody can get into the server via Enterprise Manager trying to
Connect on the server yields an error:
A connection could not be established to SERVER1.
Reason: login failed for user 'sa'.
Please verify SQL Server is running and check your SQL Server Registration
properties (by right-clicking on the SERVER1 node) and try again.
Of course, if I right-click on the SERVER1 node, I get the same message.
My client remembers the password he set; I managed to use it to get into
Query Analyzer, which prompts for a login each time it opens. But no luck
with Enterprise Manager. It's just trying to log in as sa without prompting
for a pw.
What can I do?
I already tried running sp_Password to change the pw back to what it was.
Still can't connect to the SQL Server via Enterprise Manager.
Change the server registration to use Windows Auth instead of SQL Auth?
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.
|||Before connecting to server1 in EM, right click on it and edit the entry to
use the new password.
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.
|||In SQLEM, right-click on the server registration, and Edit settings. In the
dialog, change the login information.
You should probably be using Windows Authentication and if you can not
because you are remoting, create another login that only you know the
password to and use that to login. No one should ever use the sa account
unless there is an emergency. Each user should have their own SQL Server
login created, or only Windows Authenticated, which would give each user
their own login--unless, of cours, you share your network logins too.
Sincerely,
Anthony Thomas
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.
|||Sorry, you said "remembers" and somehow I read "doesn't remember"...
You can right-click the server, "Edit SQL Server Registration Properties..."
and change the password there.
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.

Change password to sa user from remote client

Hello,
We need to change passwords to sa users from remote clients. The
problem is of course that we need to encrypt the password.
What is the best way to do this?
We read about applying a secure connection with sql server using SSL,
IPSec, multiprotocol. We implement them but we would like to know if
there is a way (maybe an api) that allow to change passwords and
encrypt them.
Yaron Paryanty
Cyber Ark CompanyI hope your asking about encrypting a session, and not using 'sa' for your
application...
By default if there is a Server certificate installed on SQL, we do encrypt
the logins on the wire. To be more secure you can Force Protocol
encryption on the client, which will encrypt all the traffic on the wire.
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||First of all thank you for your answer.
I am asking about encrypting the session but after i logon to the
mssql server i want to alter the password of sa user (or any other
user). If i sent the new password as is it will send by plained text.
I implement SSL and it works fine but i wanted to know if there is
something easier - like an API which change the password and send it
encrypted.
Thanks,
Yaron Paryanty
Cyber-Ark|||Protocol encryption is the way to do this safely. We' re able to encrypt
over all the protocols in SQL 2000. In previous versions we could only
encrypt the data using Multiprotocol.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

change number of CALs?

I have a client who has just purchased more SQL CALs. Where do we input
this new number of CALs to reflect that?
Thanks.I think you go to Control Panel -> license manager...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brian Rottman" <anonymous@.anonymous.net> wrote in message
news:%23tnkG03CEHA.3132@.TK2MSFTNGP11.phx.gbl...
> I have a client who has just purchased more SQL CALs. Where do we input
> this new number of CALs to reflect that?
> Thanks.|||I found that but the only type of license that is listed there is for
Windows.

> I think you go to Control Panel -> license manager...
>

change mdf to sql authentication

I have a web app, designed using VWD Express and using SQL 2005 Express. It has a mdf database called "client". It was created using security of windows authentication. I have already changed my instance of sql server express to accept mixed windows and sql authentication.

How do I change my "client" database's permissions to the sql authentication? I need to add a user name and password as a requirement for my web host to be able to attach the mdf to their instance of sql 2005. It has to be the same user and password as I use with my web host's sql server 2005.

Thank you in advance.

Hi mlg74,

Not very sure what you mean. Do you mena that, since you are going to upload your database to web hoster, you want to create a sql user name and password for your database?

If my understanding is correct, based on my understanding,you have no need to do that. Database authentication is managed by database administration tools, not your database file itself(the mdf file).So, you can just upload your mdf file to the web host and ask for a sql user name & password.

BTW, since you were using windows authentication before, you need to modify your connection string after authentication mode changed to "sql authentication". You can do that by simply remove "Intergrated Security=True" and add "User ID=username;Password=psw".

Sample: <add name="constr_name" connectionString="Data Source=Server IP;Initial Catalog="DatabaseName";User ID=User Name;Password=PSW"
providerName="System.Data.SqlClient" />

If you also want to use sql authentication mode on your local machine, make sure you have sql management studio express downloaded https://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en. You can create a new sql authentication name/password through Security-->logins-->New logins in Management Studio Express.

Hope my suggestion helps

|||

I know you are right, but my webhoster obviously doesn't know what they are doing. They told me I had to have the same usr name and password on my local machine as I do on the sqlserver 2005 on their server. But I am using windows authentication on my local express edition, but they said I had to make the passwords match anyway. The first time I uploaded the database files to the server for them to attach, I could no longer access my database on their server. So that's when I changed my authentication to mixed mode and had them re-attach it. But your saying it makes no difference, becasue those values are not stored in the .mdf, or .ldf files at all.

What I did to finally make it work, was after they attached it, again I could not access it on their server. So out of frustration, I deleted it off their server and was going to start over. But when I created a new db on the server, I used the same password and username, and when it created, it was my attached db files, so I was happy! That was weird, and it's odd, how some webhosters dont know how to handle sql server 2005.