How to Connect to remote sql express from client comp/app?

Hi,

I think this is a taff problem for me when I connect to remote sql express from .net application

I use Connection String:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microDB;Integrated Security=SSPI;Persist Security Info=True;User ID=micro;Password=micro

OR

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp/application

"... Login failed foruser MYCOMP\Guest... "

How to setup a user in the sql expss server to support Client/Server Application?

Anyone can help me?

regards.

md5

hi,

please have a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2177202&SiteID=1&mode=1

regards

|||

Hi Andrea,

Ow.. I understand that.. its Loud and Clear...
Evrything goes Ok now. Thanks So munch for yur help.

regards
mudiasa sanatan (md5)

How to Connect to Analysis Services from Client Management Studio?

From my client machine, I have no trouble accessing the Database Engine of my 2005 server in SQL Server Management Studio since I can use SQL Server Authentication. However, Analysis Services (at least how my server is configured right now) only allows Windows Authentication and I can connect. For what it's worth, I can't connect to the Database Engine either on my client using Windows Authentication.

What do I need to do to connect to Analysis Services from my client Management Studio?Like AS2K (and in fact all previous versions of OLAP and Analysis Services), we only support trusted connections from users to AS (both for queries and for administration/operators).

When Analysis Services itself connects to the backend RDBMS, that can be either trusted or SQL logins.

_-_-_ Dave|||

Dave Wickert wrote:

Like AS2K (and in fact all previous versions of OLAP and Analysis Services), we only support trusted connections from users to AS (both for queries and for administration/operators).

When Analysis Services itself connects to the backend RDBMS, that can be either trusted or SQL logins.

_-_-_ Dave

Dave, thanks for the reply. So how do I enable "trusted" logins for AS, if that's possible that is?|||I figured it out.
I had to make an exception for port 2383 (or whatever the default port is for Analysis Services) in Windows Firewall on the Analysis Services server.|||

Dear Dave ,

I am slightly less experienced , and wonder if you could answer some questions for me .

I am running MsSQL2005 and AS on a server .I have built cubes in server .Have had great difficulty in browsing the cube from any remote machine .

Initial problems were associated with the AS server not being able to authenticate the client .

I then put the server and client into the same domain .

I registered an existing user in the domain , into the server users , as member of the various sql groups (olap and other ) .

In the server (AS) user groups , should there be an OLAP administrator group.? I see only an OLAP user group ?

I also granted this domain user login to the sqlserver .

The client is now able to connect to the sqlserver and the Analysis server , using "windows authentication" with one catch .(Previously the cliebnt could connect to the sqlserver using username -pwd authentication .

There are no AS databases listed , no cubes shown etc . The client also has no authority to create a new database in the analysis server .

I am just wondering , what are all the prerequisites to get the client to be able to view the analysis service cubes ?

How can one verify that you have correctly setup the windows authentication required for AS , while you are logged into the server and sql manager ?

What is the correct setting for datasource property ?

Sorry for making such a long story .

any comments appreciated

How to Connect to Analysis Services from Client Management Studio?

From my client machine, I have no trouble accessing the Database Engine of my 2005 server in SQL Server Management Studio since I can use SQL Server Authentication. However, Analysis Services (at least how my server is configured right now) only allows Windows Authentication and I can connect. For what it's worth, I can't connect to the Database Engine either on my client using Windows Authentication.

What do I need to do to connect to Analysis Services from my client Management Studio?
Like AS2K (and in fact all previous versions of OLAP and Analysis Services), we only support trusted connections from users to AS (both for queries and for administration/operators).

When Analysis Services itself connects to the backend RDBMS, that can be either trusted or SQL logins.

_-_-_ Dave|||

Dave Wickert wrote:

Like AS2K (and in fact all previous versions of OLAP and Analysis Services), we only support trusted connections from users to AS (both for queries and for administration/operators).

When Analysis Services itself connects to the backend RDBMS, that can be either trusted or SQL logins.

_-_-_ Dave

Dave, thanks for the reply. So how do I enable "trusted" logins for AS, if that's possible that is?
|||I figured it out.
I had to make an exception for port 2383 (or whatever the default port is for Analysis Services) in Windows Firewall on the Analysis Services server.
|||

Dear Dave ,

I am slightly less experienced , and wonder if you could answer some questions for me .

I am running MsSQL2005 and AS on a server .I have built cubes in server .Have had great difficulty in browsing the cube from any remote machine .

Initial problems were associated with the AS server not being able to authenticate the client .

I then put the server and client into the same domain .

I registered an existing user in the domain , into the server users , as member of the various sql groups (olap and other ) .

In the server (AS) user groups , should there be an OLAP administrator group.? I see only an OLAP user group ?

I also granted this domain user login to the sqlserver .

The client is now able to connect to the sqlserver and the Analysis server , using "windows authentication" with one catch .(Previously the cliebnt could connect to the sqlserver using username -pwd authentication .

There are no AS databases listed , no cubes shown etc . The client also has no authority to create a new database in the analysis server .

I am just wondering , what are all the prerequisites to get the client to be able to view the analysis service cubes ?

How can one verify that you have correctly setup the windows authentication required for AS , while you are logged into the server and sql manager ?

What is the correct setting for datasource property ?

Sorry for making such a long story .

any comments appreciated

How to Connect to a SQL Server thru a firewall

Hi, Guru,
I have a SQL Server running behind a firewall. I also have a client
installed on my local machine. How should I configure the client to
connect to the server? What port do I need to ask the network/security
admin to open on the firewall? All installation on the server and on the
client are default. I am not familiar with SQL Server, but I have some
common sense. What should I put in the "Server Alias"? The IP of the SQL
Server? What if I run a named instance on the server. Any special
configuration I need to do on Client Network Utility?
If you could show me step by step, it would be great. If you can point me
to some online reference, it will be also great.
Any of you help is appreciated.
ChopinThe default sql server tcp port is 1433. So if you have to connect to a
server behind a firewall, you have to ask the network team to open this
port. To make sure it's working you can run the following command from the
client machine:
telnet servername_or_ip 1433
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Chopin Hu" <huchopin@.yahoo.com> wrote in message
news:ux7cmyGAEHA.3452@.TK2MSFTNGP11.phx.gbl...
> Hi, Guru,
> I have a SQL Server running behind a firewall. I also have a client
> installed on my local machine. How should I configure the client to
> connect to the server? What port do I need to ask the network/security
> admin to open on the firewall? All installation on the server and on the
> client are default. I am not familiar with SQL Server, but I have some
> common sense. What should I put in the "Server Alias"? The IP of the
SQL
> Server? What if I run a named instance on the server. Any special
> configuration I need to do on Client Network Utility?
> If you could show me step by step, it would be great. If you can point
me
> to some online reference, it will be also great.
> Any of you help is appreciated.
> Chopin
>|||try this
http://support.microsoft.com/defaul...kb;en-us;287932
Br,
Mark.
"Chopin Hu" <huchopin@.yahoo.com> wrote in message
news:ux7cmyGAEHA.3452@.TK2MSFTNGP11.phx.gbl...
> Hi, Guru,
> I have a SQL Server running behind a firewall. I also have a client
> installed on my local machine. How should I configure the client to
> connect to the server? What port do I need to ask the network/security
> admin to open on the firewall? All installation on the server and on the
> client are default. I am not familiar with SQL Server, but I have some
> common sense. What should I put in the "Server Alias"? The IP of the
SQL
> Server? What if I run a named instance on the server. Any special
> configuration I need to do on Client Network Utility?
> If you could show me step by step, it would be great. If you can point
me
> to some online reference, it will be also great.
> Any of you help is appreciated.
> Chopin
>sql

How to Connect to a SQL Server thru a firewall

Hi, Guru,
I have a SQL Server running behind a firewall. I also have a client
installed on my local machine. How should I configure the client to
connect to the server? What port do I need to ask the network/security
admin to open on the firewall? All installation on the server and on the
client are default. I am not familiar with SQL Server, but I have some
common sense. What should I put in the "Server Alias"? The IP of the SQL
Server? What if I run a named instance on the server. Any special
configuration I need to do on Client Network Utility?
If you could show me step by step, it would be great. If you can point me
to some online reference, it will be also great.
Any of you help is appreciated.
ChopinThe default sql server tcp port is 1433. So if you have to connect to a
server behind a firewall, you have to ask the network team to open this
port. To make sure it's working you can run the following command from the
client machine:
telnet servername_or_ip 1433
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Chopin Hu" <huchopin@.yahoo.com> wrote in message
news:ux7cmyGAEHA.3452@.TK2MSFTNGP11.phx.gbl...
> Hi, Guru,
> I have a SQL Server running behind a firewall. I also have a client
> installed on my local machine. How should I configure the client to
> connect to the server? What port do I need to ask the network/security
> admin to open on the firewall? All installation on the server and on the
> client are default. I am not familiar with SQL Server, but I have some
> common sense. What should I put in the "Server Alias"? The IP of the
SQL
> Server? What if I run a named instance on the server. Any special
> configuration I need to do on Client Network Utility?
> If you could show me step by step, it would be great. If you can point
me
> to some online reference, it will be also great.
> Any of you help is appreciated.
> Chopin
>|||try this
http://support.microsoft.com/default.aspx?scid=kb;en-us;287932
Br,
Mark.
"Chopin Hu" <huchopin@.yahoo.com> wrote in message
news:ux7cmyGAEHA.3452@.TK2MSFTNGP11.phx.gbl...
> Hi, Guru,
> I have a SQL Server running behind a firewall. I also have a client
> installed on my local machine. How should I configure the client to
> connect to the server? What port do I need to ask the network/security
> admin to open on the firewall? All installation on the server and on the
> client are default. I am not familiar with SQL Server, but I have some
> common sense. What should I put in the "Server Alias"? The IP of the
SQL
> Server? What if I run a named instance on the server. Any special
> configuration I need to do on Client Network Utility?
> If you could show me step by step, it would be great. If you can point
me
> to some online reference, it will be also great.
> Any of you help is appreciated.
> Chopin
>

how to connect from client to SQLserver

hi group!
i have problem with connection from client to sqlserver.
and what must i do?
thanks alot.:(Part from VB: open connection and call sp for update with parameters...

May be it is a little complicated for novice...

Dim MConnection As ADODB.Connection
Dim MCommand As ADODB.Command
Dim MRecordset As ADODB.Recordset
Dim MParameter As ADODB.Parameter

Set MConnection = CreateObject("ADODB.Connection")
Set MCommand = CreateObject("ADODB.Command")

LineConnect = "Provider=SQLOLEDB;Data Source=YourServer;UID=sa;PWD=sa"
MConnection.ConnectionString = LineConnect
MConnection.Open
With MCommand
.CommandText = "estore..UpdateLogin"
.ActiveConnection = MConnection
.CommandType = adCmdStoredProc
.Parameters.Append MCommand.CreateParameter("RetVal", adInteger, adParamReturnValue)
.Parameters.Append MCommand.CreateParameter("Id", adInteger, adParamInput, , 0)
.Parameters.Append MCommand.CreateParameter("username", adLongVarChar, adParamInput, 20, UName)
.Parameters.Append MCommand.CreateParameter("loginname", adLongVarChar, adParamInput, 128, LName)
.Parameters.Append MCommand.CreateParameter("password", adLongVarChar, adParamInput, 128, LPass)
.Parameters.Append MCommand.CreateParameter("usertypeid", adInteger, adParamInput, , UType)
End With

Set MRecordset = MCommand.Execute
If MRecordset.EOF Or MRecordset.BOF Then
.....................

another one:
Dim MConnection As ADODB.Connection
Dim MCommand As ADODB.Command
Dim MRecordset As ADODB.Recordset

Set MConnection = CreateObject("ADODB.Connection")
Set MCommand = CreateObject("ADODB.Command")

LineConnect = "Provider=SQLOLEDB;Data Source=ServerName;UID=sa;PWD="
MConnection.ConnectionString = LineConnect
MConnection.Open
With MCommand
.CommandText = "select ... or call SP"
.ActiveConnection = MConnection
.CommandType = adCmdText
End With

Set MRecordset = MCommand.Execute
If MRecordset.EOF Or MRecordset.BOF Then
.....................

How to configure Reporting service for SMTP server located remotly.

Hi
I have one problem which i am not able to solve regardig the reporting
service SMTP services.
One of our client is using the SQL server 2000 along with Microsoft
Reporting Services. They need to configure SQL Reporting services for
sending reports through emails using subscription services. They don't
have any SMTP server located in their local network.
They are using external SMTP server for sending & receiving mails. How
to configure the RSReportServer.config file for the same? If we refer
documentation available in Reporting Service Books Online, it states
that we can configure using SMTPAuthenticate settings by using
"SENDUSERNAME" & "SENDPASSWORD".
ms-help://MS.RSBOL80.1033/rsadmin/htm/arp_configserver_v1_4bzl.htm
"SendUsing"
Specifies which method to use for sending messages.
To send report subscriptions through e-mail using a local POP3 server
hosted on a Windows 2003 server, you must set the value to 1.
1=sends a message using the local SMTP service pickup directory.
2=sends the message using the network SMTP service.
"SMTPAuthenticate "
Specifies an integer value that indicates the kind of authentication to
use when sending messages to an SMTP service over a TCP/IP connection.
0=no authentication.
1=basic authentication. Credentials are passed in clear text using
either sendusername and senduserpassword, or postusername and
postuserpassword fields.
2= NTML (NT LanMan) authentication. The security context of the current
process is used to authenticate the service.
If We Refer the MSDN online it states SMTPAuthenticate authorization is
not
supported.
http://msdn2.microsoftcom/ms157273.aspx
SendUsing
Specifies which method to use for sending messages. Valid values
include the following:
1=Sends a message from the local SMTP service pickup directory.
2=Sends the message from the network SMTP service.
SMTPAuthenticate
Specifies an integer value that indicates the kind of authentication to
use when sending messages to an SMTP service over a TCP/IP connection.
Valid values include the following:
0=No authentication.
1= (not supported).
2= NTLM (NT LanMan) authentication. The security context of the Report
Server Windows service is used to connect to the network SMTP server.
Please help me in matter to solve the problem. We are unable to send
mails using reporting service by using the settings stated in the
Reporting Service Books Online. Please solve the following problems.
1. Whether it is possible to use SMTP server outside the network or
not?
2. If yes what is the method of configuring SMTP services?
3. How can we send username & password to authenticate the user?
4. Send us sample RSReportServer.config if possible for the same.
Thanking youYou might want to start with checking if the firewall is allowing traffic
out of this server.
On your server, try using telnet to check for traffic.
Open command prompt.Try to type what I write after the (type)...
(Type) Telnet your-mail-server 25
reply 220 your-mail-server Microsoft ESMTP MAIL Service, Version:
5.0.2195.6713
ready at Mon, 24 Oct 2005 15:43:27 +0200
(Type) HELO your-domain
250 your-mail-server Hello [10.61.120.21]
(Type) MAIL FROM: you@.youradress.com
250 2.1.0 you@.youradress.com... Sender OK
(Type) RCPT TO: someone.else@.youradress.com
250 2.1.5 someone.else@.youraddress.com
(Type) DATA
354 Start mail input; end with <CRLF>.<CRLF>
(Type) THis is my message!
.
250 2.6.0 your-mail-server> Queued mail for delivery
Type quit to end the telnet session.
If you can't send an e-mail by telnet, your reporting services server
probably can't either.
Kaisa M. Lindahl Lervik
"msinghindia" <msinghindia@.gmail.com> wrote in message
news:1140695688.842888.246030@.f14g2000cwb.googlegroups.com...
> Hi
> I have one problem which i am not able to solve regardig the reporting
> service SMTP services.
> One of our client is using the SQL server 2000 along with Microsoft
> Reporting Services. They need to configure SQL Reporting services for
> sending reports through emails using subscription services. They don't
> have any SMTP server located in their local network.
> They are using external SMTP server for sending & receiving mails. How
> to configure the RSReportServer.config file for the same? If we refer
> documentation available in Reporting Service Books Online, it states
> that we can configure using SMTPAuthenticate settings by using
> "SENDUSERNAME" & "SENDPASSWORD".
> ms-help://MS.RSBOL80.1033/rsadmin/htm/arp_configserver_v1_4bzl.htm
> "SendUsing"
> Specifies which method to use for sending messages.
> To send report subscriptions through e-mail using a local POP3 server
> hosted on a Windows 2003 server, you must set the value to 1.
> 1=sends a message using the local SMTP service pickup directory.
> 2=sends the message using the network SMTP service.
> "SMTPAuthenticate "
> Specifies an integer value that indicates the kind of authentication to
> use when sending messages to an SMTP service over a TCP/IP connection.
> 0=no authentication.
> 1=basic authentication. Credentials are passed in clear text using
> either sendusername and senduserpassword, or postusername and
> postuserpassword fields.
> 2= NTML (NT LanMan) authentication. The security context of the current
> process is used to authenticate the service.
>
> If We Refer the MSDN online it states SMTPAuthenticate authorization is
> not
> supported.
> http://msdn2.microsoftcom/ms157273.aspx
> SendUsing
> Specifies which method to use for sending messages. Valid values
> include the following:
> 1=Sends a message from the local SMTP service pickup directory.
> 2=Sends the message from the network SMTP service.
> SMTPAuthenticate
> Specifies an integer value that indicates the kind of authentication to
> use when sending messages to an SMTP service over a TCP/IP connection.
> Valid values include the following:
> 0=No authentication.
> 1= (not supported).
> 2= NTLM (NT LanMan) authentication. The security context of the Report
> Server Windows service is used to connect to the network SMTP server.
> Please help me in matter to solve the problem. We are unable to send
> mails using reporting service by using the settings stated in the
> Reporting Service Books Online. Please solve the following problems.
> 1. Whether it is possible to use SMTP server outside the network or
> not?
> 2. If yes what is the method of configuring SMTP services?
> 3. How can we send username & password to authenticate the user?
> 4. Send us sample RSReportServer.config if possible for the same.
> Thanking you
>|||Thnks for early reply.
I followed your steps. When type RCPT TO: mail@.domain.com gives the
following error.
"Relaying Denied...proper authentication required."
Please ! It will be great if you could let me know the reasons behind
the error.
Thanks in Advance
Manjeet|||Relaying is often used for spamming - someone sends out loads of email from
a mail server they don't own. So a lot of email providers will configure
their server to not allow relaying.
You might want to check with the owners of your smtp server what settings
you should use, and how to authenticate proberly remotely.
Kaisa M. Lindahl Lervik
"msinghindia" <msinghindia@.gmail.com> wrote in message
news:1140704270.023492.174060@.j33g2000cwa.googlegroups.com...
> Thnks for early reply.
> I followed your steps. When type RCPT TO: mail@.domain.com gives the
> following error.
> "Relaying Denied...proper authentication required."
> Please ! It will be great if you could let me know the reasons behind
> the error.
> Thanks in Advance
> Manjeet
>|||I have checked with the owners of the server. They can not enable
relaying since it will create problem of SPAM mails as server is live
on internet. So i can not send mails without authorization. So i need
to send username & password to SMTP server when Reporting Server send
mails? But how to do that '
If i refer documents of reporting service for rsconfig file it says
using "sendusername" and "senduserpassword". But it is not working.
When i refer MSDN online it says these is not Supported.
Please any body know how to authorize SQL Reporting Service for sending
mails'
Thanks in advance.

How to compile/translate a SSIS package in Japanese

Hi,

I have a requirement to do a data migration work for a Japanese client. The problem is I don't know Japanese and the client SME does not know English. So, my requirement is, I should be able to create a SSIS package in English and then should be able to compile or translate it into Japanese so that my Japanese SME can understand the package. Is it possible to do? If so, how can I do it?

Thanks in advance.

I'm not aware of any existing tool to do the translation. You can either do "bi-lingual package", e.g. use English task and components names, and the translator can add Japanese task and component descriptions. Or you can create a tool yourself using SSIS object model - it is easy to enumerate the tasks and components and rename them based on some dictionary previously created by translator.|||Thanks Michael.

How to combine different row into one row

Dear all,
I got a table with different columns of same client id as
follows:
e.g.
ClientID Name Age Country
1 Peter
1 32
1 China
Now I want to combine these three rows into one rows as
follows
ClientID Name Age Country
1 Peter 32 China
How can I do that by SQL statements. Just simple SQL
statements.
Not sotred procedures.
Thankshon123456 wrote:
> Dear all,
> I got a table with different columns of same client id as
> follows:
> e.g.
> ClientID Name Age Country
> 1 Peter
> 1 32
> 1 China
>
> Now I want to combine these three rows into one rows as
> follows
> ClientID Name Age Country
> 1 Peter 32 China
> How can I do that by SQL statements. Just simple SQL
> statements.
> Not sotred procedures.
>
> Thanks
What do the spaces in your example represent? What is the key of this
table? If those missing values are always just empty then do:
SELECT clientid,
MAX(name) AS name,
MAX(age) AS age,
MAX(country) AS country
FROM your_table
GROUP BY clientid ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
CREATE TABLE #test (col1 INT, col2 CHAR(1),col3 INT,col4 CHAR(1))
INSERT INTO #Test VALUES (1,'a',NULL,NULL)
INSERT INTO #Test VALUES (1,NULL,32,NULL)
INSERT INTO #Test VALUES (1,NULL,NULL,'b')
SELECT MAX(col1),MAX(col2),MAX(col3),MAX(col4)
FROM #test
--or
SELECT col1,
MAX(CASE WHEN col2 IS NOT NULL THEN col2 end)as col2,
MAX(CASE WHEN col3 IS NOT NULL THEN col3 end)as col3,
MAX(CASE WHEN col4 IS NOT NULL THEN col4 end)as col4
FROM #test
GROUP BY col1
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1140524835.788163.177040@.g43g2000cwa.googlegroups.com...
> Dear all,
> I got a table with different columns of same client id as
> follows:
> e.g.
> ClientID Name Age Country
> 1 Peter
> 1 32
> 1 China
>
> Now I want to combine these three rows into one rows as
> follows
> ClientID Name Age Country
> 1 Peter 32 China
> How can I do that by SQL statements. Just simple SQL
> statements.
> Not sotred procedures.
>
> Thanks
>|||SELECT ClientID, MIN(Name) as Name, Min(Age) as Age, Min(Country) as
Country
FROM Table
GROUP BY ClientID
Stu|||Thanks for you all.