change ip address and machine name

I need to change the ip address and machine name on a server which I have sql server 7.0 enterprise edition running on. Is there any specifics to be concerned over (sql registration, etc.)
thanksHi,
AFAIK SQL Server Wont bother about the IP Address. Once
you change the machine name of SQL BOx, you have to delete
the previous registration and re-register with the new
server name.
Run SQL Server 7.0 Setup from the Product CD. This will
just update SQL Server Internally to reflect new machine
name. Once you are done with that you need to update the
SQL Server by the below two stored procedures
sp_dropserver <old_name> go
sp_addserver <newname>, local go
Stop and Restart the SQL Service.
HTH
--
Regards
THIRUMAL REDDY MARAM
Sys Admin / SQL Server DBA
>--Original Message--
>I need to change the ip address and machine name on a
server which I have sql server 7.0 enterprise edition
running on. Is there any specifics to be concerned over
(sql registration, etc.)?
>thanks
>.
>sql

Change data source name

Hi all,

I have created my reports using MDB file. Now I have changed my DB to SQL Server.

I can go and change my Datasource loaction on my machine and the reports work fine. But I need to distribute these reports to all my clients.

And all have different server names.

I am using CRX and VB.NET.

TnxYou can change the location by using Datasource property(or its equivalent) in vb.net

change authentication method in a local database file

I have made a small asp.net project which uses a local database file as a part of the project. The project is running fine om my local machine, but when I upload it to the remote server, the login fails for the server.

I suspect this is can be solved by using sqlserver authentication. But I have now spent a lot of time trying to configure the database file to use this authentication mode. As I see it there are three possible solutions to the problem.

use management studio express to configure the local mdf file (Ecxept that I cant find out how to connect to the mdf-file) and from here change the authentication method to sqlserver authentication. use Visual Web developer to change the authentication method (but how?) make the windows authentication work on the server (this would probably require that mannamgement studio express connects to the remote database. (Same problem as no 1)

Help will be higly appreciated.

Bjarke

hi Bjarke,

to make things run, the account running the application server must be one of the SQL Server registered logins (at the instance level and not database level).. if your solution is hosted, I do think the IT stuff of the hosting company provides that "by default" or nothing would run, and I do not think they will grant you a standard SQL Server login... did you contact them?

regards

|||

Hello Andrea!

Maybe I have misunderstood something?

When you make an asp.net 2.0 project there is a folder (App_Data) where sit is possible via Visual web developer to add a sql database file. As I have understood it this file works as a little database server as well. Does SQL server even have to be installed on the server to make these files work as a database?

I have not contacted the hosting company about this problem, because I didnt consider it a problem. But Maybe I am wrong.

Do you know how to change the autnentication method for a local database file? I have tried a lot of things, and searched the internet for an answer, but without luck.

Thanks Bjarke

|||

hi Bjarke,

bjarke wrote:

Hello Andrea!

Maybe I have misunderstood something?

When you make an asp.net 2.0 project there is a folder (App_Data) where sit is possible via Visual web developer to add a sql database file. As I have understood it this file works as a little database server as well.

perhaps you are intending User Istances "mode", a feature available only with SQLExpress edition of SQL Server 2005.. remember that this feature is not provided by hoster that do not have SQLExpress but "traditional" SQL Server 2005 editions...

Does SQL server even have to be installed on the server to make these files work as a database?

yes, SQLExpress must be installed on the computer running the application, in your case the web application, thus the application server... when the app start's up, a User Instance of SQLExpress is generated (if not already existing)...

I have not contacted the hosting company about this problem, because I didnt consider it a problem. But Maybe I am wrong.

Do you know how to change the autnentication method for a local database file? I have tried a lot of things, and searched the internet for an answer, but without luck.

Thanks Bjarke

the "traditional" authentication "setting" can be changed using SSMSE, accessing the instance property, in the security tab or modifying a Windows registry key.. stay with the first one

the service must be restarted to use the new settings..

as regard user instances, only Windows authentication can be used with this "access mode"...

if you mean how to modify the authentication settings in the connection strings, you can have a look here..

regards

CF / SQL Server syntax error HELP

I am not sure why this is producing a SQL Server related error, but w/o
having an instance of SQL Server on my machine to verify anything further,
can you all help me with this?

<!-- validate() -->
<cffunction name="validate" access="remote" returnType="numeric">
<cfargument name="username" required="yes" type="string" />
<cfargument name="password" required="yes" type="string" /
<cfquery name="validate" datasource="#request.dsn#">
SELECT userID
FROM User
WHERE username = '#UCase(username)#'
AND password = '#UCase(password)#'
</cfquery
<cfif validate.RecordCount EQ 0>
<cfquery name="log" datasource="#request.dsn#">
UPDATE User
SET lastLoggedIn = #createOdbcDate(now())#
WHERE userId = #validate.userID#
</cfquery>
<cfreturn validate.userID />
<cfelse>
<cfreturn 0 />
</cfif>
</cffunction
Sorry that's all I can honestly provide, other than the error being on the
line with <cfif validate.RecordCount
PhilOn Tue, 14 Oct 2003 20:37:57 -0400, "Phil Powell" <soazine@.erols.com>
wrote:

>I am not sure why this is producing a SQL Server related error, but w/o
>having an instance of SQL Server on my machine to verify anything further,
>can you all help me with this?
><!-- validate() -->
> <cffunction name="validate" access="remote" returnType="numeric">
> <cfargument name="username" required="yes" type="string" />
> <cfargument name="password" required="yes" type="string" />
> <cfquery name="validate" datasource="#request.dsn#">
> SELECT userID
> FROM User
> WHERE username = '#UCase(username)#'
> AND password = '#UCase(password)#'
> </cfquery
OK you want to select the userid that has a username of #username# and
a password of #password#.

> <cfif validate.RecordCount EQ 0
Now if no user is found, update the users last login date. This code
will throw an error because validate.userID will not be defined.
Because you are trying to update a user only when no user is found.
It should not throw a database error but rather validate.userid is not
defined and that will throw an error. replace the above if statement
with the one below, and if a problem still remains please provide the
error messages given.

<cfif validate.recordCount GT 0
and now every thing should work fine.

> <cfquery name="log" datasource="#request.dsn#">
> UPDATE User
> SET lastLoggedIn = #createOdbcDate(now())#
> WHERE userId = #validate.userID#
> </cfquery>
> <cfreturn validate.userID />
> <cfelse>
> <cfreturn 0 />
> </cfif>
> </cffunction>
>Sorry that's all I can honestly provide, other than the error being on the
>line with <cfif validate.RecordCount>
>Phil

Also you can download a 120 trial of MS SQL server and if you are
running windows 2000+ you can run it.

Charliek|||> Now if no user is found, update the users last login date. This code
> will throw an error because validate.userID will not be defined.

Slight correction...Even if the query returns 0 resutlts validate.userID
will be defined it will just be blank.
The error from SQL is probably saying "Incorrect syntax near '='."
The cause of the problem is still the same...your if statement should be GT
or NEQ 0.
Side note bad form calling a table user...that is an SQL reserve word I
thought...

Adam H

"charliek" <charlie_knudsen@.hotmail.com> wrote in message
news:g4hpov4h8uauvqr91q1par5fu4g5fqeeto@.4ax.com...
> On Tue, 14 Oct 2003 20:37:57 -0400, "Phil Powell" <soazine@.erols.com>
> wrote:
> >I am not sure why this is producing a SQL Server related error, but w/o
> >having an instance of SQL Server on my machine to verify anything
further,
> >can you all help me with this?
> ><!-- validate() -->
> > <cffunction name="validate" access="remote" returnType="numeric">
> > <cfargument name="username" required="yes" type="string" />
> > <cfargument name="password" required="yes" type="string" />
> > <cfquery name="validate" datasource="#request.dsn#">
> > SELECT userID
> > FROM User
> > WHERE username = '#UCase(username)#'
> > AND password = '#UCase(password)#'
> > </cfquery>
> OK you want to select the userid that has a username of #username# and
> a password of #password#.
> > <cfif validate.RecordCount EQ 0>
> Now if no user is found, update the users last login date. This code
> will throw an error because validate.userID will not be defined.
> Because you are trying to update a user only when no user is found.
> It should not throw a database error but rather validate.userid is not
> defined and that will throw an error. replace the above if statement
> with the one below, and if a problem still remains please provide the
> error messages given.
> <cfif validate.recordCount GT 0>
> and now every thing should work fine.
> > <cfquery name="log" datasource="#request.dsn#">
> > UPDATE User
> > SET lastLoggedIn = #createOdbcDate(now())#
> > WHERE userId = #validate.userID#
> > </cfquery>
> > <cfreturn validate.userID />
> > <cfelse>
> > <cfreturn 0 />
> > </cfif>
> > </cffunction>
> >Sorry that's all I can honestly provide, other than the error being on
the
> >line with <cfif validate.RecordCount>
> >Phil
> Also you can download a 120 trial of MS SQL server and if you are
> running windows 2000+ you can run it.
> Charliek

Certificate loading issue - when creating certificate from SQL Server to SQL Server Express on t

Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.

Following script runs fine on SQL Server.

Code Snippet

use master

Create Master Key Encryption BY Password = '45Gme*3^&fwu';

BACKUP MASTER KEY TO FILE = 'C:\ServiceBroker\PrivateKeyMasterB.pvk'

ENCRYPTION BY PASSWORD = '45Gme*3^&fwu'

Create Certificate EndPointCertificateC

WITH Subject = 'C.Server.Local',

START_DATE = '06/01/2006',

EXPIRY_DATE = '01/01/2008'

ACTIVE FOR BEGIN_DIALOG = ON;

BACKUP CERTIFICATE EndPointCertificateC

TO FILE = 'C:\ServiceBroker\EndPointCertificateC.cer'

Following script runs on SQL Server Express:

Code Snippet

Create Certificate EndPointCertificateC

From FILE = 'C:\ServiceBroker\EndPointCertificateC.cer'

WITH PRIVATE KEY (

FILE = 'C:\ServiceBroker\PrivateKeyMasterB.pvk',

DECRYPTION BY PASSWORD = '45Gme*3^&fwu'

);

If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong.

Any help is appreciated. Thank you,

This must be a permisssion issue. The SQL Server Express service account does not have access to the .cer and/or .pvk file.|||

We did give permission to $SQLServerExpress to the folders where we generate the files. And all we are trying to perform this using sa account, I am not sure which other permission we are missing.

|||

Folder permissions are not enough. The files created (*.cer and *.pvk) will be ACL-ed by the creator instance to prevent any other account access, overwriting any permsision inherited from the folder permissions. So if for instance the SQL Express is running as NETWORK SERVICE, or as a (domain) user account, then it will not be able to see the files. You must explicitly grant read permissions on the two created files. Why this work on all other machines is probably because on all other machines the SQL Express and the Enterprise instance run as the same account, but not on this machine.

The account you log in as ('sa') has no relevance, all that matter here is the service account.

Certificate loading issue - when creating certificate from SQL Server to SQL Server Express on t

Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.

Following script runs fine on SQL Server.

Code Snippet

use master

Create Master Key Encryption BY Password ='45Gme*3^&fwu';

BACKUP MASTER KEYTOFILE='C:\ServiceBroker\PrivateKeyMasterB.pvk'

ENCRYPTION BY PASSWORD ='45Gme*3^&fwu'

CreateCertificate EndPointCertificateC

WITH Subject ='C.Server.Local',

START_DATE ='06/01/2006',

EXPIRY_DATE ='01/01/2008'

ACTIVE FOR BEGIN_DIALOG =ON;

BACKUPCERTIFICATE EndPointCertificateC

TOFILE='C:\ServiceBroker\EndPointCertificateC.cer'

Following script runs on SQL Server Express:

Code Snippet

CreateCertificate EndPointCertificateC

FromFILE='C:\ServiceBroker\EndPointCertificateC.cer'

WITH PRIVATE KEY(

FILE='C:\ServiceBroker\PrivateKeyMasterB.pvk',

DECRYPTION BY PASSWORD ='45Gme*3^&fwu'

);

If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong.

Any help is appreciated. Thank you,

This must be a permisssion issue. The SQL Server Express service account does not have access to the .cer and/or .pvk file.|||

We did give permission to $SQLServerExpress to the folders where we generate the files. And all we are trying to perform this using sa account, I am not sure which other permission we are missing.

|||

Folder permissions are not enough. The files created (*.cer and *.pvk) will be ACL-ed by the creator instance to prevent any other account access, overwriting any permsision inherited from the folder permissions. So if for instance the SQL Express is running as NETWORK SERVICE, or as a (domain) user account, then it will not be able to see the files. You must explicitly grant read permissions on the two created files. Why this work on all other machines is probably because on all other machines the SQL Express and the Enterprise instance run as the same account, but not on this machine.

The account you log in as ('sa') has no relevance, all that matter here is the service account.

Cell Security : Help

SECURITY USING CELL-SECURITY:

From what i've read cell security s enforced on the client. If someone is able to gain access to a machine running the client (for example an application server or a web server) he is able to get cell values independently of the fact that those values will be defined as #N/A in the secured cell value property. The real value is travelling between theAnalysis Server and the application server. Is this true ? How can we effectively garantee true security ?Did you look in BOL?

Cell Security
In a cube role, you can implement cell security to limit the cube cells that end users in the role can view as they browse cubes. You can also grant read/write access to a write-enabled cube and limit the cells that end users in the role can update. You do this by selecting a policy and by selecting a rule or defining a custom rule for each permission.

Cell security is optional. If you do not specify cell security, end users see all cell values in cubes they are authorized to access. (However, if dimension security is specified, cells for some members might not be viewable.) If a cube is write-enabled, end users cannot update cell values. If one or more of a virtual cube's component cubes are write-enabled, end users cannot update the cell values of virtual cubes.

If a policy or rule permits updates to a cell, it can be updated if it is an atomic cell. If the cell is not atomic, it can be updated only if the client application provides a way of dispersing the update over the subordinate atomic cells. For example, in a client application a write-enabled cube is displayed with the lowest level of every dimension except Time. On the axis for the Time dimension, the nonatomic cells for months are displayed, but the subordinate atomic cells for days are not. (Days is the lowest level in the Time dimension.) A cell for June can be updated by adding $90 if the client application provides a way of dividing the +$90 update into thirty +$3 updates, one to each of the cells for the 30 days in June. Dispersion methods other than simple division can also be used. The UPDATE CUBE statement provides several methods. For more information, see UPDATE CUBE Statement.

CE vs MS RS

Hi Teros
Just one question, is there any reason why CE and RS could be installed on
the one machine, I have limited resources at hand, and I wish to prove my
case for RS, however, I worried of corrupting my test installation of CE.
Would there be any conflict errors?
Kind Regards
RikeshI don't use CE but I have seen posting where this is discussed and I know
that a technique people have used is to have both up at one time while they
are designing replacements. So, I think you would be safe to do this.
Bruce L-C
"rikesh" <rikesh_patel@.website.com> wrote in message
news:et5ROOWeEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi Teros
> Just one question, is there any reason why CE and RS could be installed on
> the one machine, I have limited resources at hand, and I wish to prove my
> case for RS, however, I worried of corrupting my test installation of CE.
> Would there be any conflict errors?
> Kind Regards
>
> Rikesh
>|||Cool Bruce, thanks for your reply...
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OQHpPLYeEHA.1356@.TK2MSFTNGP09.phx.gbl...
> I don't use CE but I have seen posting where this is discussed and I know
> that a technique people have used is to have both up at one time while
they
> are designing replacements. So, I think you would be safe to do this.
> Bruce L-C
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:et5ROOWeEHA.384@.TK2MSFTNGP10.phx.gbl...
> > Hi Teros
> >
> > Just one question, is there any reason why CE and RS could be installed
on
> > the one machine, I have limited resources at hand, and I wish to prove
my
> > case for RS, however, I worried of corrupting my test installation of
CE.
> > Would there be any conflict errors?
> >
> > Kind Regards
> >
> >
> > Rikesh
> >
> >
>