connecting to server but not to database-Newbie Question

Hi everyone,

I created a database in SSMS,but I can't connect to it using VB Express code.When I delete the database name from the server I can connect to server,but when I mention the name of the database or initial catalog,I receive a login failure error for the mentioned database...

My connection string is:

Dim conn1 As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;initial catalog=TEST1;" _

& "Integrated Security=True;" & "user instance=true;")

Everything is Local,I am using express version of SQL server and VB.How I am not be able to connect the database I created under the server in SSMS...I am really confused...

Thanks in advance!!!!

Can

What is the exact error message you are getting ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hallo Mr Suessmeyer,

Yesterday you had also replied my question but I can't go ayn further.I downloaded SSMS,craeted a database under the server but I still get this error...How can I gain the access rights to the database that I created?

" Die von der Anmeldung angeforderte "database1"-Datenbank kann nicht ge?ffnet werden. Fehler bei der Anmeldung.
Fehler bei der Anmeldung für den Benutzer 'ADPLAN\atalay'. "

This was from the company's PC (where I write my thesis) I have a windows user account there...I also tried at home,I am the only user for windows XP at home but anyways I can not connect to the database,receiving the same error message.As I had written before if I leave Initial catalog= or database= fields empty then I am able to connect to the server...

Thanky in advance!

Can

|||

For a quick one, try to add the user to the sysadmin group to let him access the database (if it is based on a security issue)

sp_addsrvrolemember [ @.loginame= ] 'login'
, [ @.rolename = ] 'role'

For granular configuration, use the following procedure to grant him access to the db (not any objects so far)

sp_grantlogin [@.loginame=] 'login'
sp_defaultdb [ @.loginame = ] 'login', [ @.defdb = ] 'database'
sp_grantdbaccess [ @.loginame = ] 'login'
[ , [ @.name_in_db = ] 'name_in_db' [ OUTPUT ] ]

The same can be done within the Managment Studio.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you for the reply but I don't know where to enter this code...It'd be kind of you if you'd give more details,I am not a programmer which makes the things more difficult for me...

Regards,

Can

|||

Hi,

no problem, download SQL Server Express Management Studio and execute the query within the Studio (there is a function to open a "New Query". Then type in the command with the appropiate values fitting to your production system.
Download via: http://msdn.microsoft.com/vstudio/express/sql/download/

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

hi,

I do think all this is related to the fact you are just using a user instance (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp)..

on the other side, when you execute SSMSE it usually connects to the "traditional" SQLExpress edition you installed, and these are 2 different instances..

regards

|||

Thank you for all your replies.I solved my problem using SQL server management studio and using this query:

Use TEST1
go
exec sp_addlogin loginname,password,TEST1
go
exec sp_helplogins
go

then I added to this login to the database I wanted to connect,and I had to add login name and password to connection string(instead of user instances and integrated security).

Now it works this way.

Regards,

Can

Connecting to remote Express instance using SSMS

I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
the default instance and two SQLExpress instances named 'sqlexpress' and
'officeservers', all withing SSMS, and can connect and manage them.
From my workstation, running SQL 2005 Dev, SP2, I can connect to the default
instance on that server, but cannot connect to the express instances. They
both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
I have done the following:
Made myself a member of the local Admin group
Tried using the sa account to connect
nothing seems to work. What am I missing?
TIA
Todd Chittenden
Is the SQLBrowser Service enabled and started?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
> the default instance and two SQLExpress instances named 'sqlexpress' and
> 'officeservers', all withing SSMS, and can connect and manage them.
> From my workstation, running SQL 2005 Dev, SP2, I can connect to the
> default
> instance on that server, but cannot connect to the express instances. They
> both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
> I have done the following:
> Made myself a member of the local Admin group
> Tried using the sa account to connect
> nothing seems to work. What am I missing?
> TIA
> --
> Todd Chittenden
>
|||I found it not long after posting the original question:
No, the Browser service was not running. Starting that up enabled me to
connect to it. So that was the answer.
But even before the Enterprise instance was installed, (which includes the
browser service) I was not able to connect. I know I have other machines in
my domain that are running SQL Express. Should I not be able to connect to
them using my SSMS? What do I need to do to make that happen?
I assume that I need membership in the group on the local machine that bears
the SQL Express instance name?
What else? I can't rely on the Browser service in those instances.
Thanks
Todd Chittenden
"Roger Wolter[MSFT]" wrote:

> Is the SQLBrowser Service enabled and started?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>
|||SQL Express by default installs as a named instance. Unless you specify the
TCP/IP port in the connection string, you need the sqlbrowser service to
connect to a named instance.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:F4B981A2-4B9A-443E-855B-D53B89FCE511@.microsoft.com...[vbcol=seagreen]
>I found it not long after posting the original question:
> No, the Browser service was not running. Starting that up enabled me to
> connect to it. So that was the answer.
> But even before the Enterprise instance was installed, (which includes the
> browser service) I was not able to connect. I know I have other machines
> in
> my domain that are running SQL Express. Should I not be able to connect to
> them using my SSMS? What do I need to do to make that happen?
> I assume that I need membership in the group on the local machine that
> bears
> the SQL Express instance name?
> What else? I can't rely on the Browser service in those instances.
> Thanks
> --
> Todd Chittenden
>
> "Roger Wolter[MSFT]" wrote:

Connecting to remote Express instance using SSMS

I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
the default instance and two SQLExpress instances named 'sqlexpress' and
'officeservers', all withing SSMS, and can connect and manage them.
From my workstation, running SQL 2005 Dev, SP2, I can connect to the default
instance on that server, but cannot connect to the express instances. They
both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
I have done the following:
Made myself a member of the local Admin group
Tried using the sa account to connect
nothing seems to work. What am I missing?
TIA
--
Todd ChittendenIs the SQLBrowser Service enabled and started?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
> the default instance and two SQLExpress instances named 'sqlexpress' and
> 'officeservers', all withing SSMS, and can connect and manage them.
> From my workstation, running SQL 2005 Dev, SP2, I can connect to the
> default
> instance on that server, but cannot connect to the express instances. They
> both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
> I have done the following:
> Made myself a member of the local Admin group
> Tried using the sa account to connect
> nothing seems to work. What am I missing?
> TIA
> --
> Todd Chittenden
>|||I found it not long after posting the original question:
No, the Browser service was not running. Starting that up enabled me to
connect to it. So that was the answer.
But even before the Enterprise instance was installed, (which includes the
browser service) I was not able to connect. I know I have other machines in
my domain that are running SQL Express. Should I not be able to connect to
them using my SSMS? What do I need to do to make that happen?
I assume that I need membership in the group on the local machine that bears
the SQL Express instance name?
What else? I can't rely on the Browser service in those instances.
Thanks
--
Todd Chittenden
"Roger Wolter[MSFT]" wrote:

> Is the SQLBrowser Service enabled and started?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>|||SQL Express by default installs as a named instance. Unless you specify the
TCP/IP port in the connection string, you need the sqlbrowser service to
connect to a named instance.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:F4B981A2-4B9A-443E-855B-D53B89FCE511@.microsoft.com...[vbcol=seagreen]
>I found it not long after posting the original question:
> No, the Browser service was not running. Starting that up enabled me to
> connect to it. So that was the answer.
> But even before the Enterprise instance was installed, (which includes the
> browser service) I was not able to connect. I know I have other machines
> in
> my domain that are running SQL Express. Should I not be able to connect to
> them using my SSMS? What do I need to do to make that happen?
> I assume that I need membership in the group on the local machine that
> bears
> the SQL Express instance name?
> What else? I can't rely on the Browser service in those instances.
> Thanks
> --
> Todd Chittenden
>
> "Roger Wolter[MSFT]" wrote:
>

Connect using different Windows user name...?!?!

I've scoured Google searching for an answer that seems like it should be
easy but apparently isn't...when I open SSMS to connect to a SQL 2005
database and choose Windows authentication, it greys out the User Name
box...problem is, the server I need to connect to is in another domain...how
on earth are you supposed to specify a different Windows user name to
connect with other than the one currently logged into the machine?

-Ben

--
Posted via a free Usenet account from http://www.teranews.comOn Jul 6, 3:12 am, "Ben Hanson" <transparency...@.hotmail.comwrote:

Quote:

Originally Posted by

I've scoured Google searching for an answer that seems like it should be
easy but apparently isn't...when I open SSMS to connect to a SQL 2005
database and choose Windows authentication, it greys out the User Name
box...problem is, the server I need to connect to is in another domain...how
on earth are you supposed to specify a different Windows user name to
connect with other than the one currently logged into the machine?
>
-Ben
>
--
Posted via a free Usenet account fromhttp://www.teranews.com

Hi Ben,

If you need to connect to another machine within your own domain, or
in a domain with the appropriate trust relationship to your own
domain, then you can use the Run As feature when right clicking on the
shortcut that you're using to launch SSMS.

If, OTOH, it isn't possible for users from the target domain to log
onto machines on your local domain, then you need to find some other
way to do it - normal route to take in that case would be to get a
Terminal Services connection to a machine within the target domain and
then start SSMS normally (Or at least, that's what I've normally done
in the past).

The point behind this is - when SSMS offers to connect using Windows
Authentication, it's using Authentication that has already been
established. There's nothing built in to SSMS/SQL Server to perform
the authentication itself.

Damien

Connect to SSIS on clustered sql2k5

I got following error when try to expand msdb under SSIS in SSMS, the SSIS I connected to is on clustered server. The windows account used is member of local admin on both nodes in the cluster and sysadmin in sql. I tried with host name that SSIS runs on and virtual sql server name for connection, tried connect from remote client machine and server itself, got same error:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

Anyone knows what's happen? Thanks.

I solve the problem by changing <ServerName>.</ServerName> to <ServerName>virtual_sql_server_name</ServerName> in %ms sql server%\90\dts\binn\MsDtsSrvr.ini.xml file on each node.|||

I have configured SSIS on the cluster after clustered DB installation. I have followed the directions on 'How to: Configure Integration Services on a Cluster' doc on msdn (http://msdn2.microsoft.com/en-us/library/ms345193.aspx) but I am still not able to connect and I get this error message (very similar to the message on beginning of this post)

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)

BUTTONS:

OK

Appreciate any help you can provide.

Thanks!

Connect to SSIS on clustered sql2k5

I got following error when try to expand msdb under SSIS in SSMS, the SSIS I connected to is on clustered server. The windows account used is member of local admin on both nodes in the cluster and sysadmin in sql. I tried with host name that SSIS runs on and virtual sql server name for connection, tried connect from remote client machine and server itself, got same error:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

Anyone knows what's happen? Thanks.

I solve the problem by changing <ServerName>.</ServerName> to <ServerName>virtual_sql_server_name</ServerName> in %ms sql server%\90\dts\binn\MsDtsSrvr.ini.xml file on each node.|||

I have configured SSIS on the cluster after clustered DB installation. I have followed the directions on 'How to: Configure Integration Services on a Cluster' doc on msdn (http://msdn2.microsoft.com/en-us/library/ms345193.aspx) but I am still not able to connect and I get this error message (very similar to the message on beginning of this post)

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)

BUTTONS:

OK

Appreciate any help you can provide.

Thanks!