LINK SQL SERVER DATABASE FROM ACCESS

[Savas Ates]

i tried link to sql server db from access and i failed.... (What is my
fault.. I think It doenst ask me catalog -database name so it doesnt accept
my username and password..It cant find my username and password which
database has it ..)

i follewed these steps...

1.right click and link table
2.chooes types of data -->ODBC Databases
3.Machine(or Computer) Data Source and Click New from here..
4.Choose User Data Source and click NEXT
5.Chooes SQL Server and click NEXT
6.Click END
7.enter Server IP description and Name and click NEXT
8.Chooes Sql Server Authentication and Username and password of my catalog
database... and CLICK NEXT
9.SQL State='010000'
Sql Server Failure:10060
[microsoft [[ODBC SQL Server Driver][TCP/IP Sockets] ConnectionOpen
(Connect())
Connection failed
Sql State: '08001'
Sql Server Failure:17
[microsoft [[ODBC SQL Server Driver][TCP/IP Sockets] Sql server doesnt
exists or access denied..

how can i solve it or can link a sql server database

 

[Norman Yuan]

Problem lies in either step 7 or step 8.

Step 7: where is the SQL Server located? If it is on your local network,
simply enter "SeverName" or "ServerName\SQLInstanceName", If it is on an
WinXP Pro with SP2, does the TCP/IP port (1433) is allowed through the
firewall (if it is enabled).

If your are sure the network setup is correct, then it is Step 8: you did
not give correct login credential. SQL Server uses Win security (no need to
supply username/password) or SQL Security (you must supply username/pswd).
However, even your login credential is correct, it does not necessarily mean
that you can access a database on the SL Server. The administrator can give
or deny access to a database for a login (whether it is Win Security or SQL
Security). You may have check with the DB administrator to make sure the
login you use has access to the database.

 

[Savas Ates]

my sql server is not in local network.. it is in remote network... and its
ip , username and password is true...
coz i can access it from enterprise manager from my local sql server...

i looked at my permissions.. from users in my database ...

in my dbo public and db_owner permit checked... what is tthe problem ?





tried link to sql server db from access and i failed.... (What is my
fault.. I think It doenst ask me catalog -database name so it doesnt accept
my username and password..It cant find my username and password which
database has it ..)

i follewed these steps...

1.right click and link table
2.chooes types of data -->ODBC Databases
3.Machine(or Computer) Data Source and Click New from here..
4.Choose User Data Source and click NEXT
5.Chooes SQL Server and click NEXT
6.Click END
7.enter Server IP description and Name and click NEXT
8.Chooes Sql Server Authentication and Username and password of my catalog
database... and CLICK NEXT
9.SQL State='010000'
Sql Server Failure:10060
[microsoft [[ODBC SQL Server Driver][TCP/IP Sockets] ConnectionOpen
(Connect())
Connection failed
Sql State: '08001'
Sql Server Failure:17
[microsoft [[ODBC SQL Server Driver][TCP/IP Sockets] Sql server doesnt
exists or access denied..

how can i solve it or can link a sql server database

 

[Norman Yuan]

So, your Enterprise Manager is connected to this remove SQL Server. Then it
is Step 8. it must be the username/password, which is an Login's
authentication credential.

In your EM, go to the "SQL Server->Security->Logins", right-click the login
you are to use and select "Prooerties". Select tab "Database Access" and
check one or more database you'd like this login to have access and set
appropriate database roles. Also, you may want to reset this login's
password to make sure you'll use correct password.

After this, you can retry to create an ODBC data source name for the Access
linking.

 

[Savas Ates]

i made what u said but there is not any tab database access .. There is
only general Tab...

what i must do ?

 

[Norman Yuan]

Are you sure you clicked correct object?

It is the node on left: Console Root->Microsoft SQL Server->SQL Server
Group->(server name)->Security->Logins".
On right, right click any Login listed and "Properties". The "Properties"
dialog has three tabs: "General", "Server Roles" and "Database Access".

If you did not see three tab, are you really using Enterprise Manager?, or
you right-clicked a user under database, which "Properties" dialog has only
"General" tab.

 

[Savas Ates]

my database is a remote one.. i right click other users i see only help
dialog..

i right click my user and i click and i can see properties dialog..i see
only general tab.. can it be due to my admin's settings.. what he and i must
do solve this problem... sure i user enteprrise manager by the way

when i open my database-->users--> on the right side i can see dbo... i
right click and chooes properties .. but i cant change my settings (i see
only general tab.. )
dont forget that my first attemp is not same with this.. i made what u said
truly...

by the way which roles or permission must be given my database ?