Connecting to SQL database

I am moving my site from a shared environment to a dedicated server and SQL is on the same box. How do I connect to my database if it's on the same box?in your connection string, reference your data source or server as "." or (local)|||

Thank you. This is my first time setting up a SQL server. Is there a special login account I need to set up for my web pages to use?

|||you can use integrated security... if your running win2003, the account will be [network service], if xp pro, [aspnet]
if you want to use sql server authentication, add a user to your server, make the user a dbo over your database. but this way, you have to put the username and pw into the query string. :)
http://www.connectionstrings.com/ <-- that might help|||

I just uploaded a training video that covers this. :)
http://www.dotnetnukerocks.com/Administering/GettingStarted/Installation/DotNetNukev40InstallVideo/tabid/2574/Default.aspx

Connecting to Database on a Server

Hi,

I'm new to SQLExpress, and I need some help. I am trying to create a SQL database that will sit on a shared server in our organisation. I go into SSMSE and click on New Database which takes me to the New database screen. I now want to change the default path name to our server name, but when I click on the Locate Folder button (...), it only allows me to select folders from my Local drive. Is this a limitation of SQL Express, is there a way around this...?

Thanks in advance...

You will need to install the engine onto the server which you plan to use, then use the following MS Article to allow remote connections to the server. Then you should use the Management tool to connect to the new server and configure the application paths.

|||

Thanks Glenn,

Even if I do all that, I can't connect to any database except for the ones on my hard drive. I have even tried attaching databases in SSMSE and it only allows you to attach Local databases. There is nowhere where I can connect to a drive other than C.

Connecting to an Access Database

I am trying to stablish a shared data source to an Access database for my reporting services. Is this possible? I only see connection type Microsoft SQL Server as Connection Type.. OLEDB is not recognized. How can I achieve this?

Hi,

Create a system ODBC data source for your access db from Control Panel Administrative tools.

Create a shared ODBC data souce in RS and select the data source you just created in Control panel.

After that it is business as usual. You may have to finetune some quirks for deployment, the host server should have the ODBC data source as well. An idea would be to try to use an UNC when defining the ODBC datasource and post your access db in a share in some server. That would give more portability.

Philippe

Connecting to an Access Database

I am trying to stablish a shared data source to an Access database for my reporting services. Is this possible? I only see connection type Microsoft SQL Server as Connection Type.. OLEDB is not recognized. How can I achieve this?

Hi,

Create a system ODBC data source for your access db from Control Panel Administrative tools.

Create a shared ODBC data souce in RS and select the data source you just created in Control panel.

After that it is business as usual. You may have to finetune some quirks for deployment, the host server should have the ODBC data source as well. An idea would be to try to use an UNC when defining the ODBC datasource and post your access db in a share in some server. That would give more portability.

Philippe

CONNECTING SQL 2005 SERVER FROM A REMOTE MACHINE PROBLEM

I installed SQL 2005 with some settings which is below.
INSTANCE NAME: CAVCAV
Protocols For CAVCAV :
Shared Memory : Enabled
Named Pipes : Enabled , Pipe Name :\\.\pipe\MSSQL$CAVCAVsql\query
TCP/IP : Enabled Yes
Ip address :
Ip1
active:yes
enabled :yes
Ip Adress : 212.154.38.146
Tcp Dynamic Ports :
Tcp Port : 1433
Ip2
active:yes
enabled :yes
Ip Adress :127.0.0.1
Tcp Dynamic Ports :
Tcp Port : 1433
IpAll
Tcp Dynamic Ports :
Tcp Port : 1433
VIA : Disabled
Sql Native Client Configuration
Client Protocols
Shared Memory : Enabled
Named Pipes : Enabled , Default Pipe :sql\query
VIA : Disabled
Telnet 212.154.38.146 1433 (Success)
I can connect to server from my local MSSMS (SQL 2005.)
When I try to connect vie DSN or Access (Linked Table Manager) Or SQL 2000
Enterprise Manager I cant connect.
When I write Only Ip address which is 212.154.38.146 on System DSN
I get
Attempting connection
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.
TESTS FAILED!
When I write 212.154.38.146\CAVCAV
I get
Microsoft SQL Server ODBC Driver Version 03.86.3959
Running connectivity tests...
Attempting connection
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.
TESTS FAILED!
How can i connect and manage my Sql 2005 remotely. Any help will be
appreciated.Did you enable remote connections?
MC
<in da club> wrote in message news:epyXh4PAIHA.484@.TK2MSFTNGP06.phx.gbl...
>I installed SQL 2005 with some settings which is below.
> INSTANCE NAME: CAVCAV
> Protocols For CAVCAV :
> Shared Memory : Enabled
> Named Pipes : Enabled , Pipe Name :\\.\pipe\MSSQL$CAVCAVsql\query
> TCP/IP : Enabled Yes
> Ip address :
> Ip1
> active:yes
> enabled :yes
> Ip Adress : 212.154.38.146
> Tcp Dynamic Ports :
> Tcp Port : 1433
> Ip2
> active:yes
> enabled :yes
> Ip Adress :127.0.0.1
> Tcp Dynamic Ports :
> Tcp Port : 1433
> IpAll
> Tcp Dynamic Ports :
> Tcp Port : 1433
>
> VIA : Disabled
> Sql Native Client Configuration
> Client Protocols
> Shared Memory : Enabled
> Named Pipes : Enabled , Default Pipe :sql\query
> VIA : Disabled
>
> Telnet 212.154.38.146 1433 (Success)
> I can connect to server from my local MSSMS (SQL 2005.)
> When I try to connect vie DSN or Access (Linked Table Manager) Or SQL 2000
> Enterprise Manager I cant connect.
> When I write Only Ip address which is 212.154.38.146 on System DSN
> I get
> Attempting connection
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.
> TESTS FAILED!
> When I write 212.154.38.146\CAVCAV
> I get
> Microsoft SQL Server ODBC Driver Version 03.86.3959
> Running connectivity tests...
> Attempting connection
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> TESTS FAILED!
> How can i connect and manage my Sql 2005 remotely. Any help will be
> appreciated.
>
>
>
>
>
>
>
>|||Go to SQL Server Surface Area Configuration and check your instance if
Remote Connections is enabled as TCP\IP as MC mentioned.
Remote Connections feature is disabled by default for Developer and Express
Editions.
And check out if Browser Service is running and use
<computer_name>\<instance_name> in your connection strings.
--
Ekrem Önsoy
<in da club> wrote in message news:epyXh4PAIHA.484@.TK2MSFTNGP06.phx.gbl...
>I installed SQL 2005 with some settings which is below.
> INSTANCE NAME: CAVCAV
> Protocols For CAVCAV :
> Shared Memory : Enabled
> Named Pipes : Enabled , Pipe Name :\\.\pipe\MSSQL$CAVCAVsql\query
> TCP/IP : Enabled Yes
> Ip address :
> Ip1
> active:yes
> enabled :yes
> Ip Adress : 212.154.38.146
> Tcp Dynamic Ports :
> Tcp Port : 1433
> Ip2
> active:yes
> enabled :yes
> Ip Adress :127.0.0.1
> Tcp Dynamic Ports :
> Tcp Port : 1433
> IpAll
> Tcp Dynamic Ports :
> Tcp Port : 1433
>
> VIA : Disabled
> Sql Native Client Configuration
> Client Protocols
> Shared Memory : Enabled
> Named Pipes : Enabled , Default Pipe :sql\query
> VIA : Disabled
>
> Telnet 212.154.38.146 1433 (Success)
> I can connect to server from my local MSSMS (SQL 2005.)
> When I try to connect vie DSN or Access (Linked Table Manager) Or SQL 2000
> Enterprise Manager I cant connect.
> When I write Only Ip address which is 212.154.38.146 on System DSN
> I get
> Attempting connection
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.
> TESTS FAILED!
> When I write 212.154.38.146\CAVCAV
> I get
> Microsoft SQL Server ODBC Driver Version 03.86.3959
> Running connectivity tests...
> Attempting connection
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> TESTS FAILED!
> How can i connect and manage my Sql 2005 remotely. Any help will be
> appreciated.
>
>
>
>
>
>
>
>|||Remote Connections is enabled
Local and Remote Connections
* using both tcp/ip and named pipes
Im able to connect it from my local SQL 2005 Management Studio too.
Sql Server Browser Service is running too. I use Enterprise edition.|||Did you check your firewall settings? Be sure 1433 is allowed.
--
Ekrem Önsoy
<in da club> wrote in message news:ODZjlkQAIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Remote Connections is enabled
> Local and Remote Connections
> * using both tcp/ip and named pipes
> Im able to connect it from my local SQL 2005 Management Studio too.
> Sql Server Browser Service is running too. I use Enterprise edition.
>|||I can
Telnet 212.154.38.146 1433 with Success . Im sure it is open .
One more thing when i netstat -an
there are some records like that
127.0.0.1:1000 127.0.0.0:1433 TIME_WAIT
127.0.0.1:1001 127.0.0.0:1433 TIME_WAIT
.
.
127.0.0.1:3000 127.0.0.0:1433 TIME_WAIT
Im sure dynamic tcp port is blank and TCP port is 1433
Im very confused. Almost everthing seems ok but im not able to connect it
via DSN and SQL 2000 Server.|||I solved the problem.
If you use only named instance you need 1434 udp port open + 1435 tcp
2) SQL Browser service: which is a replacement of SSRP system in SQL Server
2000, run as a Windows Service on installation of SQL 2k5. Upon startup, SQL
Server Browser starts and claims UDP port 1434. When SQL Server 2000 and SQL
Server 2005 clients request SQL Server resources, the client network library
sends a UDP message to the server using port 1434. SQL Server Browser
responds with the TCP/IP port or named pipe of the requested instance.
Therefore, you need to make sure SQL Browser is enabled and started when
remote client ask for which tcp port or pipe name SQL Server is listening
on. If your remote SQL Instance is a default instance, you do not necessary
enable sql browser since client would always try default TCP port 1433 and
pipe name \\<remoterserver>\pipe\sql\query. But, if you have SQL 2k5 named
instance installed or SQL 2000 and SQL 2k5 side by side installed, you must
enable and start sqlbrowser.
II. Problem list:
By understanding background 1) and 2), I belive you can imagine issues you
may face when make remote connection against SQL 2k5:
1) Fail to connect over TCP/IP or Named Pipe if the request protocol was
not enabled.
2) Fail to connect over TCP/IP or Named Pipe if Firewall enabled on the
remote server and tcp port or "File and Printer Sharing" is not added to
the Firewall exception list.
3) Fail to connect to remote sql named instance if SQL Browser was not
enabled or UDP port 1434 that browser listening on is added to the Firewall
exception list.